COUNT with CASE

Transact-SQL
1

I need to calculate the average number of days a person has stayed with us, when they have left during the reporting period (nobody else should be counted). The days they started and left should be counted, hence my +1 in the DATEDIFF clause below.

I have created a select statement to list these by person:

--List Length of Stay over quarter 
select 
		distinct pd.NHI_NUMBER
		,ppr.ReferralStartDate
		,ppr.ReferralEndDate
		,datediff(dd,ppr.ReferralStartDate,ppr.ReferralEndDate)+1 as[No Days]
        from PATIENT_PRIMHD_REFERRAL as ppr
			join patient_details as pd
			on pd.PATIENT_ID=ppr.PATIENT_ID
			join PATIENT_PRIMHD_ACTIVITY as ppa
			on ppa.REFERRAL_SEQNO=ppr.SEQNO
		where 
			ppr.ReferralTeamID=@TeamID
			and ppr.ReferralEndDate between @StartDate and @EndDate
			and ppa.ActivityStartDate between @StartDate and @EndDate
order by
	pd.NHI_NUMBER

and this is the result, which is correct as I have checked the source data:

I have averaged the days in Excel - the answer is 124.

So then I created a query to count and average this data:

--Average Length of Stay over quarter - Non residential
select case 
		when [Count]=0 then 'N/A' 
		else [No days]/[Count]
		end as [Average Length of Stay]
  from 
	(select
		count(distinct pd.NHI_NUMBER) as [Count]
		,sum(datediff(dd,ppr.ReferralStartDate,ppr.ReferralEndDate))
		+count(distinct pd.NHI_NUMBER) as [No days]
        from PATIENT_PRIMHD_REFERRAL as ppr
			join patient_details as pd
			on pd.PATIENT_ID=ppr.PATIENT_ID
			join PATIENT_PRIMHD_ACTIVITY as ppa
			on ppa.REFERRAL_SEQNO=ppr.SEQNO
		where 
			ppr.ReferralTeamID=@TeamID
			and ppr.ReferralEndDate between @StartDate and @EndDate
			and ppa.ActivityStartDate between @StartDate and @EndDate
       ) as a
;

but this query's result is 1777

I cannot fathom why it doesn't work, so hopefully someone else can :slight_smile:

2

I am not sure why you got answer like that. Please try the below query once

select AVG(datediff(dd,ppr.ReferralStartDate,ppr.ReferralEndDate) +count(distinct pd.NHI_NUMBER)) as [Average Length of Stay]

4

Your query didnt' work, but I have fixed it with this one - so thanks for making me try again :slight_smile:

--Average Length of Stay over quarter - Non residential
SELECT 
	avg([Length of Stay]) as [Average Length of Stay]
FROM 
	(
	SELECT DISTINCT pd.NHI_NUMBER
	,ppr.ReferralStartDate
	,ppr.ReferralEndDate
	,(datediff(dd, ppr.ReferralStartDate, ppr.ReferralEndDate) +1)
	as [Length of Stay]
	FROM
	PATIENT_PRIMHD_REFERRAL AS ppr
	JOIN patient_details AS pd ON pd.PATIENT_ID = ppr.PATIENT_ID
	JOIN PATIENT_PRIMHD_ACTIVITY AS ppa ON ppa.REFERRAL_SEQNO = ppr.SEQNO
	where ppr.ReferralTeamID = @TeamID
		AND ppr.ReferralEndDate BETWEEN @StartDate
			AND @EndDate
		AND ppa.ActivityStartDate BETWEEN @StartDate
			AND @EndDate
	) as t

The average is right now.

5

Why the "+" before COUNT? Shouldn't it be "/" ?

6

The "+" is to add 1 to each person's average length of stay - as the requirement is that the first and last day is counted, but when I use a datediff it only gives me the difference between the two dates - essentially one day short of the requirement.

7

No, I am talking about this expression

select
count(distinct pd.NHI_NUMBER) as [Count]
,sum(datediff(dd,ppr.ReferralStartDate,ppr.ReferralEndDate))
+count(distinct pd.NHI_NUMBER) as [No days]
from PATIENT_PRIMHD_REFERRAL as ppr

Why the plus after the SUM? You sum up all DATEDIFFs and then add unique number of NHI_Numbers? Shouldnt the query look like this instead?

select
count(distinct pd.NHI_NUMBER) as [Count]
,sum(datediff(dd,ppr.ReferralStartDate,ppr.ReferralEndDate))
/ count(distinct pd.NHI_NUMBER) as [No days]
from PATIENT_PRIMHD_REFERRAL as ppr

8

See my previous reply.