# COUNT with CASE

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

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]

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

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

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

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.

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