SQLTeam.com | Weblogs | Forums

Convert/cast


#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)


#5

Why not just use ROUND()?

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


#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.