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