Convert/cast

Transact-SQL
1

Hi

I am trying to provide an average length of stay from this set of data, I have pasted it into Excel and calculated the average, which I want to round up.

So I want SQL to calculate this as 4, not 3.9 or 3

My SQL Query:

--Average Length of Stay over quarter
select case 
		when [Count]=0 then 0
		else [No days]/[Count]
		end as [Average Length of Stay]
from (
  select
	count (ppa.PATIENT_ID) as [Count]
	,sum(
	case
	WHEN ppa.activitystartdate < @StartDate and ppa.activityenddate < @StartDate
		THEN null
	when ppa.activitystartdate > @EndDate 
		THEN null
	when ppa.activitystartdate <= @StartDate and ppa.activityenddate <= @EndDate
		then datediff(d,@StartDate,ppa.activityenddate)+1
	when ppa.activitystartdate <= @StartDate and ppa.activityenddate >= @EndDate
		then datediff(d,@StartDate,@EndDate)
	when ppa.activitystartdate >= @StartDate 
		and datediff(dd,ppa.activitystartdate,@enddate) <=1
		then 1
	when ppa.activitystartdate >= @StartDate and ppa.activityenddate <= @EndDate
		then datediff(d,ppa.activitystartdate,ppa.activityenddate)+1
	when ppa.activitystartdate >= @StartDate and ppa.activityenddate >= @EndDate
		then datediff(d,ppa.activitystartdate,@EndDate)+1
	end
	) as [No Days]
FROM 
	PATIENT_PRIMHD_activity as ppa
where
	case
	WHEN ppa.activitystartdate < @StartDate and ppa.activityenddate < @StartDate
		THEN null
	when ppa.activitystartdate > @EndDate 
		THEN null
	when ppa.activitystartdate <= @StartDate and ppa.activityenddate <= @EndDate
		then datediff(d,@StartDate,ppa.activityenddate)+1
	when ppa.activitystartdate <= @StartDate and ppa.activityenddate >= @EndDate
		then datediff(d,@StartDate,@EndDate)
	when ppa.activitystartdate >= @StartDate 
		and datediff(dd,ppa.activitystartdate,@enddate) <=1
		then 1
	when ppa.activitystartdate >= @StartDate and ppa.activityenddate <= @EndDate
		then datediff(d,ppa.activitystartdate,ppa.activityenddate)+1
	when ppa.activitystartdate >= @StartDate and ppa.activityenddate >= @EndDate
		then datediff(d,ppa.activitystartdate,@EndDate)+1
	end is not null
--Filter by House
	and ppa.referralteamID=@ResID
	and ppa.ActivityTypeCodeID=60
	and ppa.ActivitySettingID=173
--Only during period
	and (ppa.activityStartDate between @StartDate and @EndDate
	or ppa.activityEndDate between @StartDate and @EndDate)
) as a

However, the result it shows is always 3.

I have had a look into using CONVERT to convert the calculated fields to decimal, but cannot get the formula to work.

I have tried:

select case 
		when [Count]=0 then 0
		else convert(decimal(3,2),[No days]/[Count])
		end as [Average Length of Stay]
from ( etc.. as above

and the result is 3.00

Can anyone shed some light on this please? I want it to round up, but I could do that manually if it would just show me the decimal places.

2

In your formular you are using integers only, and thus the result will be integer.
Introducing a float/decimal into the formular, will produce a float/decimal result.
Also, all "outputs" from the case statement, should be of same type.
So this should give you 3.9:

select case
          when [Count]=0 then 0.0
          else 1.0*[No days]/[Count]
       end as [Average Length of Stay]

Always rounding up, can be done like this:

select case
          when [Count]=0 then 0
          else [No days]/[Count]
              +sign((1.0*[No days]/[Count])%1)
       end as [Average Length of Stay]
3

Thanks! Brilliant!

When I use either of your options, I get this as a result,. How do I limit the number of decimal places

Option 1: 3.726190476190

Option 2: 4.000000000000

4

If you want integers as result, wrap the case statement in a cast or convert:
cast(case ... end as int)
convert(int,case ... end)

1 Like
5

Why not just use ROUND()?

ROUND(AVERAGE(MyField), 0) [MyField]

1 Like
6

Hmm, I read the question as "always round up".
If you want normal round, of course you should use the round function.

7

Thanks, yes I did want it rounded up.

Is there a way to use a variation of the Swedish rounding system (ie. round down for decimals of 0.1, 0.2, 0.3, 0.4 and round up for decimals of 0.6, 0.7, 0.8 and 0.9?

8

That is what the default ROUND function does. I am not familiar with the Swedish system. What do you want to happen if the value happens to be 0.5?

9

Unusually, the 0.5's are left to the discretion of the retailer (this is used in Supermarkets over here) - but I would want it to round up. Does ROUND do that?

10

Yes. By default, anything less than 0.5 will be rounded down, and 0.5 and higher will be rounded up. See this example

DECLARE @x1 FLOAT = 17.50001, @x2 FLOAT = 17.50, @x3 FLOAT = 17.49999;
SELECT ROUND(@x1,0), ROUND(@x2,0), ROUND(@x3,0);
11

Actually what I tried to do in my first reply, was always round up (ex. 3.00001 would be 4). My method works only on positive numbers and I just remembers a much better way ---> use the ceiling function - that is, if you always want to round up to highest integer. In this case, you want to use round function as @BBarn suggested.
Sorry for the confusion I created.