Average across columns

Margo · May 21, 2015, 1:49am

Hi,

I need to create an average across three calculated columns, where I have used CASE expressions to calculate each month's average days stay by clients in our facilities.

The CASE expression is somewhat convoluted because the database somehow puts a rubbish date into the pr.end_date field when the pr.open_stay box is ticked 'Y'. Don't ask me why, someone else built the application and the tables don't even have keys...

Anyway, the averages are correct (I have checked the raw data) but I don't know how to get it to average across the columns (which I know SQL can't do in a simple query, but there are ways and means...)

I have tried applying the answers from other queries but I can't seem to make them work in my query.

My query:

use [exoMashTrust-test]
declare @StartDate date , @EndDate date
,@StartDateMonth1 date, @StartDateMonth2 date, @StartDateMonth3 date
,@EndDateMonth1 date, @EndDateMonth2 date, @EndDateMonth3 date
,@TestDateStart date, @TestDateEnd date
,@NumberBeds int
,@LevelCode varchar(50)
---
set @StartDate = '20150101 00:00:00.000'
set @EndDate= '20150331 23:59:59.999'
set @StartDateMonth1 = '20150101 00:00:00.000'
set @EndDateMonth1= '20150131 23:59:59.999'
set @StartDateMonth2 = '20150201 00:00:00.000'
set @EndDateMonth2= '20150228 23:59:59.999'
set @StartDateMonth3 = '20150301 00:00:00.000'
set @EndDateMonth3= '20150331 23:59:59.999'
set @LevelCode = 'MHL2PN'
--Average Length of Stay for all 3 months
select
	avg(case
	WHEN PR.START_DATE < @StartDateMonth1 and pr.end_date < @StartDateMonth1
		and pr.OPEN_STAY='N'
		THEN null
	when PR.START_DATE > @EndDateMonth1 
		THEN null
	when pr.start_date <= @StartDateMonth1 and pr.end_date <= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth1,pr.end_date)+1
	when pr.start_date <= @StartDateMonth1 and pr.end_date >= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth1,@EndDateMonth1)+1
	when pr.START_DATE <= @StartDateMonth1 and pr.OPEN_STAY='Y'
		then datediff(d,@StartDateMonth1,@EndDateMonth1)+1
	when pr.START_DATE >= @StartDateMonth1 and pr.end_date <= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,pr.end_date)+1
	when pr.START_DATE >= @StartDateMonth1 and pr.end_date >= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,@EndDateMonth1)+1
	when pr.START_DATE >= @StartDateMonth1 and pr.OPEN_STAY='Y'
		then datediff(d,pr.START_DATE,@EndDateMonth1)+1
	end) as [Month 1]
	,avg(case
	WHEN PR.START_DATE < @StartDateMonth2 and pr.end_date < @StartDateMonth2
		and pr.OPEN_STAY='N'
		THEN null
	when PR.START_DATE > @EndDateMonth2 
		THEN null
	when pr.start_date <= @StartDateMonth2 and pr.end_date <= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth2,pr.end_date)+1
	when pr.start_date <= @StartDateMonth2 and pr.end_date >= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth2,@EndDateMonth2)+1
	when pr.START_DATE <= @StartDateMonth2 and pr.OPEN_STAY='Y'
		then datediff(d,@StartDateMonth2,@EndDateMonth2)+1
	when pr.START_DATE >= @StartDateMonth2 and pr.end_date <= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,pr.end_date)+1
	when pr.START_DATE >= @StartDateMonth2 and pr.end_date >= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,@EndDateMonth2)+1
	when pr.START_DATE >= @StartDateMonth2 and pr.OPEN_STAY='Y'
		then datediff(d,pr.START_DATE,@EndDateMonth2)+1
	end) as [Month2]
	,avg(case
	WHEN PR.START_DATE < @StartDateMonth3 and pr.end_date < @StartDateMonth3
		and pr.OPEN_STAY='N'
		THEN null
	when PR.START_DATE > @EndDateMonth3 
		THEN null
	when pr.start_date <= @StartDateMonth3 and pr.end_date <= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth3,pr.end_date)+1
	when pr.start_date <= @StartDateMonth3 and pr.end_date >= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth3,@EndDateMonth3)+1
	when pr.START_DATE <= @StartDateMonth3 and pr.OPEN_STAY='Y'
		then datediff(d,@StartDateMonth3,@EndDateMonth3)+1
	when pr.START_DATE >= @StartDateMonth3 and pr.end_date <= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,pr.end_date)+1
	when pr.START_DATE >= @StartDateMonth3 and pr.end_date >= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,@EndDateMonth3)+1
	when pr.START_DATE >= @StartDateMonth3 and pr.OPEN_STAY='Y'
		then datediff(d,pr.START_DATE,@EndDateMonth3)+1
	end) as [Month3]
from
PATIENT_ROOMS as pr
join PATIENT_DETAILS as pd
on pr.PATIENT_ID=pd.PATIENT_ID
join PATIENT_ETH_TYPES as pet
on pd.ETHNICITY=pet.SEQNO
where
--Filter by Level
	pr.STOCKCODE=@LevelCode
having
	avg(case
	WHEN PR.START_DATE < @StartDateMonth1 and pr.end_date < @StartDateMonth1
		and pr.OPEN_STAY='N'
		THEN null
	when PR.START_DATE > @EndDateMonth1 
		THEN null
	when pr.start_date <= @StartDateMonth1 and pr.end_date <= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth1,pr.end_date)+1
	when pr.start_date <= @StartDateMonth1 and pr.end_date >= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth1,@EndDateMonth1)+1
	when pr.START_DATE <= @StartDateMonth1 and pr.OPEN_STAY='Y'
		then datediff(d,@StartDateMonth1,@EndDateMonth1)+1
	when pr.START_DATE >= @StartDateMonth1 and pr.end_date <= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,pr.end_date)+1
	when pr.START_DATE >= @StartDateMonth1 and pr.end_date >= @EndDateMonth1
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,@EndDateMonth1)+1
	when pr.START_DATE >= @StartDateMonth1 and pr.OPEN_STAY='Y'
		then datediff(d,pr.START_DATE,@EndDateMonth1)+1
	end) is not null
	and avg(case
	WHEN PR.START_DATE < @StartDateMonth2 and pr.end_date < @StartDateMonth2
		and pr.OPEN_STAY='N'
		THEN null
	when PR.START_DATE > @EndDateMonth2 
		THEN null
	when pr.start_date <= @StartDateMonth2 and pr.end_date <= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth2,pr.end_date)+1
	when pr.start_date <= @StartDateMonth2 and pr.end_date >= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth2,@EndDateMonth2)+1
	when pr.START_DATE <= @StartDateMonth2 and pr.OPEN_STAY='Y'
		then datediff(d,@StartDateMonth2,@EndDateMonth2)+1
	when pr.START_DATE >= @StartDateMonth2 and pr.end_date <= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,pr.end_date)+1
	when pr.START_DATE >= @StartDateMonth2 and pr.end_date >= @EndDateMonth2
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,@EndDateMonth2)+1
	when pr.START_DATE >= @StartDateMonth2 and pr.OPEN_STAY='Y'
		then datediff(d,pr.START_DATE,@EndDateMonth2)+1
	end) is not null
	and avg(case
	WHEN PR.START_DATE < @StartDateMonth3 and pr.end_date < @StartDateMonth3
		and pr.OPEN_STAY='N'
		THEN null
	when PR.START_DATE > @EndDateMonth3 
		THEN null
	when pr.start_date <= @StartDateMonth3 and pr.end_date <= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth3,pr.end_date)+1
	when pr.start_date <= @StartDateMonth3 and pr.end_date >= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,@StartDateMonth3,@EndDateMonth3)+1
	when pr.START_DATE <= @StartDateMonth3 and pr.OPEN_STAY='Y'
		then datediff(d,@StartDateMonth3,@EndDateMonth3)+1
	when pr.START_DATE >= @StartDateMonth3 and pr.end_date <= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,pr.end_date)+1
	when pr.START_DATE >= @StartDateMonth3 and pr.end_date >= @EndDateMonth3
		and pr.OPEN_STAY='N'
		then datediff(d,pr.START_DATE,@EndDateMonth3)+1
	when pr.START_DATE >= @StartDateMonth3 and pr.OPEN_STAY='Y'
		then datediff(d,pr.START_DATE,@EndDateMonth3)+1
	end) is not null
;

This produces the following table:

Month 1 Month2 Month3
31 27 28

All I want is the average across these three months.

Thanks

Margo

waterduck · May 21, 2015, 2:47am

(Month 1 + Month 2 + Month 3) / 3?

Margo · May 21, 2015, 2:55am

I can't see where to put that in the code

waterduck · May 21, 2015, 3:27am

avg(case
WHEN PR.START_DATE < @StartDateMonth1 and pr.end_date < @StartDateMonth3
and pr.OPEN_STAY='N'
THEN null
when PR.START_DATE > @EndDateMonth3
THEN null
when pr.start_date <= @StartDateMonth1 and pr.end_date <= @EndDateMonth3
and pr.OPEN_STAY='N'
then datediff(d,@StartDateMonth1,pr.end_date)+1
when pr.start_date <= @StartDateMonth1 and pr.end_date >= @EndDateMonth3
and pr.OPEN_STAY='N'
then datediff(d,@StartDateMonth1,@EndDateMonth3)+1
when pr.START_DATE <= @StartDateMonth3 and pr.OPEN_STAY='Y'
then datediff(d,@StartDateMonth1,@EndDateMonth3)+1
when pr.START_DATE >= @StartDateMonth1 and pr.end_date <= @EndDateMonth3
and pr.OPEN_STAY='N'
then datediff(d,pr.START_DATE,pr.end_date)+1
when pr.START_DATE >= @StartDateMonth1 and pr.end_date >= @EndDateMonth3
and pr.OPEN_STAY='N'
then datediff(d,pr.START_DATE,@EndDateMonth3)+1
when pr.START_DATE >= @StartDateMonth3 and pr.OPEN_STAY='Y'
then datediff(d,pr.START_DATE,@EndDateMonth3)+1
end) as [Month3]

Margo · May 21, 2015, 3:51am

Thanks for that suggestion, however it is giving me an average of 35, when infact the average can never be more than the maximum number of days in a month (which is 31).

Hopefully someone else can come up with a solution.

madhivanan · May 21, 2015, 9:48am

One method is to use derived table

select (Month 1 + Month 2 + Month 3) / 3 from
(
your select statement
) as t

bitsmed · May 21, 2015, 8:04pm

madhivanan's method would work.

Here's an alternative (with a little bit of optimization):

declare @StartDate  date
       ,@EndDate1 date
       ,@EndDate2 date
       ,@EndDate3 date
       ,@Levelcode  varchar(50);

set @StartDate='20150101 00:00:00.000';
set @EndDate1=dateadd(mm,1,@StartDate);
set @EndDate2=dateadd(mm,2,@StartDate);
set @EndDate3=dateadd(mm,3,@StartDate);
set @LevelCode='MHL2PN';

select avg(datediff(dd
                   ,case
                       when r.start_date<@StartDate
                       then @StartDate
                       else r.start_date
                    end
                   ,case
                       when r.open_stay='Y' or r.end_date>=@EndDate1
                       then @EndDate1
                       else r.end_date
                    end
                   )
          ) as month1
      ,avg(datediff(dd
                   ,case
                       when r.start_date<@EndDate1
                       then @EndDate1
                       else r.start_date
                    end
                   ,case
                       when r.open_stay='Y' or r.end_date>=@EndDate2
                       then @EndDate2
                       else r.end_date
                    end
                   )
          ) as month2
      ,avg(datediff(dd
                   ,case
                       when r.start_date<@EndDate2
                       then @EndDate2
                       else r.start_date
                    end
                   ,case
                       when r.open_stay='Y' or r.end_date>=@EndDate3
                       then @EndDate3
                       else r.end_date
                    end
                   )
          ) as month3
      ,avg(datediff(dd
                   ,case
                       when r.start_date<@StartDate
                       then @StartDate
                       else r.start_date
                    end
                   ,case
                       when r.open_stay='Y' or r.end_date>=@EndDate3
                       then @EndDate3
                       else r.end_date
                    end
                   )
          )/3 as month_all
  from patient_rooms as r
       inner join patient_details as d
               on d.patient_id=r.patientid
       inner join patient_eth_types as t
               on t.seqno=d.ethnicity
 where r.stockcode=@LevelCode
   and r.start_date<@EndDate3)
   and (r.end_date>@StartDate
    or  e.open_stay='Y'
       )

Margo · May 22, 2015, 2:00am

Thanks bitsmed,

I think I follow most of the logic, and it's a very clever way of building a select statement into a datediff BUT it's giving me averages that aren't right. I think it's how the case statements are calculated.

Results from your query are:

month1	month2	month3	month_all
22	23	28	26

And the results from my query (which I have checked by manually averaging the source data) are :

Average Length of Stay Month 1	Average Length of Stay Month2	Average Length of Stay Month3
31                                  	27	                   28

Calculated average of all three is 28.66 (so 29 - or does SQL round down?)

So I actually need an average of the three months, not an average over the whole stay divided by three.

Is there some way to use the case statements to SET the average of each month (@averageMonth1 ) as a variable?

bitsmed · May 22, 2015, 4:54pm

Second attempt:

declare @StartDate date
       ,@EndDate1  date
       ,@EndDate2  date
       ,@EndDate3  date
       ,@Levelcode varchar(50);

set @StartDate='20150101';
set @EndDate1=dateadd(mm,1,@StartDate);
set @EndDate2=dateadd(mm,2,@StartDate);
set @EndDate3=dateadd(mm,3,@StartDate);
set @LevelCode='MHL2PN';

select case when month1count=0 then 0 else month1sum/month1count end as month1
      ,case when month2count=0 then 0 else month2sum/month2count end as month2
      ,case when month3count=0 then 0 else month3sum/month3count end as month3
      ,case
          when month1count+month2count+month3count=0
          then 0
          else (month1days+month2days+month3days)/(month1count+month2count+month3count)
       end as month_all
  from (select sum(case
                      when r.start_date<@EndDate1
                      then datediff(dd
                                   ,case
                                       when r.start_date<@StartDate
                                       then @StartDate
                                       else r.start_date
                                    end
                                   ,case
                                       when r.open_stay='Y' or r.end_date>@EndDate1
                                       then @EndDate1
                                       else dateadd(dd,1,r.end_date)
                                    end
                                   )
                      else 0
                   end
                  ) as month1days
              ,sum(case
                      when r.start_date<@EndDate1
                      then 1
                      else 0
                   end
                  ) as month1count
              ,sum(case
                      when r.start_date<@EndDate2
                       and (r.open_stay='Y'
                        or  r.end_date>=@EndDate1
                           )
                      then datediff(dd
                                   ,case
                                       when r.start_date<@EndDate1
                                       then @EndDate1
                                       else r.start_date
                                    end
                                   ,case
                                       when r.open_stay='Y' or r.end_date>@EndDate2
                                       then @EndDate2
                                       else dateadd(dd,1,r.end_date)
                                    end
                                   )
                      else 0
                   end
                  ) as month2days
              ,sum(case
                      when r.start_date<@EndDate2
                       and (r.open_stay='Y'
                        or  r.end_date>=@EndDate1
                           )
                      then 1
                      else 0
                   end
                  ) as month2count
              ,sum(case
                      when r.open_stay='Y' or r.end_date>=@EndDate2
                      then datediff(dd
                                   ,case
                                       when r.start_date<@EndDate2
                                       then @EndDate2
                                       else r.start_date
                                    end
                                   ,case
                                       when r.open_stay='Y' or r.end_date>@EndDate3
                                       then @EndDate3
                                       else dateadd(dd,1,r.end_date)
                                    end
                                   )
                      else 0
                   end
                  ) as month3days
              ,sum(case
                      when r.open_stay='Y' or r.end_date>=@EndDate2
                      then 1
                      else 0
                   end
                  ) as month3count
          from patient_rooms as r
               inner join patient_details as d
                       on d.patient_id=r.patientid
               inner join patient_eth_types as t
                       on t.seqno=d.ethnicity
         where r.stockcode=@LevelCode
           and r.start_date<@EndDate3
           and (r.end_date>=@StartDate
            or  r.open_stay='Y'
               )
       ) as a

Margo · May 24, 2015, 8:56pm

Thanks bitsmed, I think we're getting there...

select case when month1count=0 then 0 else month1sum/month1count end as month1
,case when month2count=0 then 0 else month2sum/month2count end as month2
,case when month3count=0 then 0 else month3sum/month3count end as month3

These lines are giving me these errors:

Msg 207, Level 16, State 1, Line 14
Invalid column name 'month1sum'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'month2sum'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'month3sum'

bitsmed · May 24, 2015, 9:18pm

I'm sorry, month1sum should be month1days. So code should be:

declare @StartDate date
       ,@EndDate1  date
       ,@EndDate2  date
       ,@EndDate3  date
       ,@Levelcode varchar(50);

set @StartDate='20150101';
set @EndDate1=dateadd(mm,1,@StartDate);
set @EndDate2=dateadd(mm,2,@StartDate);
set @EndDate3=dateadd(mm,3,@StartDate);
set @LevelCode='MHL2PN';

select case when month1count=0 then 0 else month1days/month1count end as month1
      ,case when month2count=0 then 0 else month2days/month2count end as month2
      ,case when month3count=0 then 0 else month3days/month3count end as month3
      ,case
          when month1count+month2count+month3count=0
          then 0
          else (month1days+month2days+month3days)/(month1count+month2count+month3count)
       end as month_all
  from (select sum(case
                      when r.start_date<@EndDate1
                      then datediff(dd
                                   ,case
                                       when r.start_date<@StartDate
                                       then @StartDate
                                       else r.start_date
                                    end
                                   ,case
                                       when r.open_stay='Y' or r.end_date>@EndDate1
                                       then @EndDate1
                                       else dateadd(dd,1,r.end_date)
                                    end
                                   )
                      else 0
                   end
                  ) as month1days
              ,sum(case
                      when r.start_date<@EndDate1
                      then 1
                      else 0
                   end
                  ) as month1count
              ,sum(case
                      when r.start_date<@EndDate2
                       and (r.open_stay='Y'
                        or  r.end_date>=@EndDate1
                           )
                      then datediff(dd
                                   ,case
                                       when r.start_date<@EndDate1
                                       then @EndDate1
                                       else r.start_date
                                    end
                                   ,case
                                       when r.open_stay='Y' or r.end_date>@EndDate2
                                       then @EndDate2
                                       else dateadd(dd,1,r.end_date)
                                    end
                                   )
                      else 0
                   end
                  ) as month2days
              ,sum(case
                      when r.start_date<@EndDate2
                       and (r.open_stay='Y'
                        or  r.end_date>=@EndDate1
                           )
                      then 1
                      else 0
                   end
                  ) as month2count
              ,sum(case
                      when r.open_stay='Y' or r.end_date>=@EndDate2
                      then datediff(dd
                                   ,case
                                       when r.start_date<@EndDate2
                                       then @EndDate2
                                       else r.start_date
                                    end
                                   ,case
                                       when r.open_stay='Y' or r.end_date>@EndDate3
                                       then @EndDate3
                                       else dateadd(dd,1,r.end_date)
                                    end
                                   )
                      else 0
                   end
                  ) as month3days
              ,sum(case
                      when r.open_stay='Y' or r.end_date>=@EndDate2
                      then 1
                      else 0
                   end
                  ) as month3count
          from patient_rooms as r
               inner join patient_details as d
                       on d.patient_id=r.patientid
               inner join patient_eth_types as t
                       on t.seqno=d.ethnicity
         where r.stockcode=@LevelCode
           and r.start_date<@EndDate3
           and (r.end_date>=@StartDate
            or  r.open_stay='Y'
               )
       ) as a

Margo · May 24, 2015, 10:00pm

Thanks so much, it works

How do I rate you a GENIUS???

bitsmed · May 24, 2015, 10:06pm

You're welcome - glad to be of assistance