AVG with DISTINCT

Margo · May 25, 2015, 3:56am

I am trying to calculate the average length of stay, where someone has not left our service.

I just want the average, not the list (which I have managed to get) as I only want the average of the DISTINCT ppr.SEQNO - not all of them.

> USE [exoMashTrust-test]
> declare 
> @StartDate date
> ,@StartDate1 date 
> ,@StartDate2 date 
> ,@StartDate3 date
> ,@EndDate Date 
> ,@EndDate1 date 
> ,@EndDate2 date 
> ,@EndDate3 date 
> ,@TeamID int
> --Edit the dates depending on the months required in the report
> set @startdate = '20150101 00:00:00.000'
> set @enddate = '20150331 23:59:59.999'
> --These are auto calculated Dates
> set @StartDate1=@StartDate;
> set @StartDate2=dateadd(mm,1,@StartDate1);
> set @StartDate3=dateadd(mm,2,@StartDate1);
> set @EndDate1=dateadd(mm,-2,@EndDate);
> set @EndDate2=dateadd(mm,-1,@EndDate);
> set @EndDate3=@EndDate;
> --Section Team ID
> set @TeamID = 29
> --Average Length of Stay over quarter 
> --This just lists the length of stay where they haven't left
> SELECT
> distinct pd.NHI_NUMBER [Client ID]
> ,PPR.SEQNO as [Referral ID]
> ,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
> ,convert(Char(10),ppr.ReferralEndDate,103) as [Referral End Date]
> ,datediff(d,ppr.ReferralStartDate, ppr.ReferralEndDate) as [No. Days]
> FROM
> 	PATIENT_PRIMHD_REFERRAL as ppr
> 	join PATIENT_PRIMHD_ACTIVITY as ppa
> 	on ppr.PATIENT_ID=ppa.PATIENT_ID
> 	join patient_details as pd
> 	on pd.PATIENT_ID=ppa.PATIENT_ID
> 	full outer join PATIENT_ETH_TYPES as pet
> 	on pd.ETHNICITY=pet.SEQNO
> 	JOIN PATIENT_PRIMHD_MHA as mha
> 	on ppa.ActivityTypeCodeID=mha.SEQNO
> WHERE
> 	ppr.ReferralEndDate between @startdate and @EndDate
> 	and ppa.ActivityStartDate between @startdate and @EndDate
> 	and ppa.ReferralTeamID=@TeamID
> 	and mha.localshortcode like '%175'
> ORDER BY
> [Client ID]
> ;

I am pretty sure I need to use an OVER and PARTITION but I can't get the syntax right (I have never created a query with these before).

Now I have calculated the average of these and it is 116 - but I cannot get this number with any of my bodgy-coding

Hopefully someone can help.

madhivanan · May 25, 2015, 11:00am

Use derived table

select avg([no of days]) as average from
(
your original select statement
) as t

Margo · May 25, 2015, 7:23pm

Sorry I don't think I know where to put that.

I have edited it to:

--Average Length of Stay over quarter 
--This just lists the length of stay where they haven't left
SELECT
avg(
distinct pd.NHI_NUMBER as [NHI Number]
,PPR.SEQNO as [Referral ID]
,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
,convert(Char(10),ppr.ReferralEndDate,103) as [Referral End Date]
,datediff(d,ppr.ReferralStartDate, ppr.ReferralEndDate) as [No. Days]) as t
FROM
	PATIENT_PRIMHD_REFERRAL as ppr
	join PATIENT_PRIMHD_ACTIVITY as ppa
	on ppr.PATIENT_ID=ppa.PATIENT_ID
	join patient_details as pd
	on pd.PATIENT_ID=ppa.PATIENT_ID
	full outer join PATIENT_ETH_TYPES as pet
	on pd.ETHNICITY=pet.SEQNO
	JOIN PATIENT_PRIMHD_MHA as mha
	on ppa.ActivityTypeCodeID=mha.SEQNO
WHERE
	ppr.ReferralEndDate between @startdate and @EndDate
	and ppa.ActivityStartDate between @startdate and @EndDate
	and ppa.ReferralTeamID=@TeamID
	and mha.localshortcode like '%174'
ORDER BY
pd.NHI_NUMBER

but I get the error

Msg 156, Level 15, State 1, Line 291
Incorrect syntax near the keyword 'as'. - Referring to the first line 

distinct pd.NHI_NUMBER as [NHI Number]

No matter where I move it, I get the same error

bitsmed · May 25, 2015, 8:56pm

select avg([No. Days]) as average from
(
SELECT
distinct pd.NHI_NUMBER [Client ID]
,PPR.SEQNO as [Referral ID]
,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
,convert(Char(10),ppr.ReferralEndDate,103) as [Referral End Date]
,datediff(d,ppr.ReferralStartDate, ppr.ReferralEndDate) as [No. Days]
FROM
	PATIENT_PRIMHD_REFERRAL as ppr
	join PATIENT_PRIMHD_ACTIVITY as ppa
	on ppr.PATIENT_ID=ppa.PATIENT_ID
	join patient_details as pd
	on pd.PATIENT_ID=ppa.PATIENT_ID
	full outer join PATIENT_ETH_TYPES as pet
	on pd.ETHNICITY=pet.SEQNO
	JOIN PATIENT_PRIMHD_MHA as mha
	on ppa.ActivityTypeCodeID=mha.SEQNO
WHERE
	ppr.ReferralEndDate between @startdate and @EndDate
	and ppa.ActivityStartDate between @startdate and @EndDate
	and ppa.ReferralTeamID=@TeamID
	and mha.localshortcode like '%175'
ORDER BY
	[Client ID]
) as t
;

Margo · May 26, 2015, 2:09am

Thanks so much

BUT... It is calculating the average as 90, and when I paste the query into a spreadsheet, and average the number of days, the average is 116 - why would it be different?

bitsmed · May 26, 2015, 3:43am

I don't have your original data, so I'm not able to run the query, but this gives the result of 116:

select avg([No. Days]) as average
  from (select 'BHJ6656' as [Client ID]
              ,6067  as [Referral ID]
              ,'26/02/2014' as [Referral Start Date]
              ,'19/03/2015' as [Referral End Date]
              ,386 as [No. Days]
        union all select 'BYL3362',7325,'30/03/2015','30/03/2015',0
        union all select 'DEH94..',7053,'06/01/2015','14/01/2015',8
        union all select 'EMN57..',7049,'06/01/2015','20/03/2015',73
        union all select 'FBL0672',7282,'20/03/2015','20/03/2015',0
        union all select 'FTT0312',7005,'15/12/2014','19/03/2015',94
        union all select 'LSM3490',7101,'22/01/2015','20/03/2015',57
        union all select 'LQL4942',6401,'09/07/2014','12/02/2015',218
        union all select 'MPL1179',6850,'18/11/2014','25/01/2015',99
        union all select 'NYW25..',6940,'01/12/2014','23/03/2015',53
        union all select 'TUK1330',5862,'13/02/2014','23/03/2015',403
        union all select 'TUK1330',7280,'20/03/2015','23/03/2015',3
       ) as t

so it should work, unless your inner select produces other result, that the one you posted earlier. You can check this, by commenting out the outer select (by the way, I removed the "order by" as this is not needed when averaging):

/*select avg([No. Days]) as average from
(*/
SELECT
distinct pd.NHI_NUMBER [Client ID]
,PPR.SEQNO as [Referral ID]
,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
,convert(Char(10),ppr.ReferralEndDate,103) as [Referral End Date]
,datediff(d,ppr.ReferralStartDate, ppr.ReferralEndDate) as [No. Days]
FROM
	PATIENT_PRIMHD_REFERRAL as ppr
	join PATIENT_PRIMHD_ACTIVITY as ppa
	on ppr.PATIENT_ID=ppa.PATIENT_ID
	join patient_details as pd
	on pd.PATIENT_ID=ppa.PATIENT_ID
	full outer join PATIENT_ETH_TYPES as pet
	on pd.ETHNICITY=pet.SEQNO
	JOIN PATIENT_PRIMHD_MHA as mha
	on ppa.ActivityTypeCodeID=mha.SEQNO
WHERE
	ppr.ReferralEndDate between @startdate and @EndDate
	and ppa.ActivityStartDate between @startdate and @EndDate
	and ppa.ReferralTeamID=@TeamID
	and mha.localshortcode like '%175'
/*) as t*/
;

Margo · May 26, 2015, 10:03pm

Yes that worked! thanks

bitsmed · May 26, 2015, 10:14pm

You're welcome