Hi,

Here is a snapshot of my table called #Referrals

The question I am trying to answer using this data is

• What % of referrals are completed within 24 hours of being received?

Can anybody help? Excluding any DocID's that doesn't have both a received time and a completed time.

Many Thanks

;with cte as (select DocId, min(case when CRSReferralStatus like '1%' then [Time] end) as Received,

max(case when CRSReferralStatus like '4%' then [Time] end) as Completed

from #Referrals GROUP BY DocId)

select *, datediff(hour, Received, Completed) as [CompletedInHours] from cte

where Received IS NOT NULL and Completed IS NOT NULL

and  Received <= dateadd(hour, -24, Completed)

To get the percent, try

;with cte as (select DocId, min(case when CRSReferralStatus like '1%' then [Time] end) as Received,

max(case when CRSReferralStatus like '4%' then [Time] end) as Completed

from #Referrals GROUP BY DocId), 

cte2 as (select *, datediff(hour, Received, Completed) as [CompletedInHours] from cte

where Received IS NOT NULL and Completed IS NOT NULL)

select COALESCE(count(case when CompletedInHours<24 then DocId end),0)*100.0/

coalesce(count(DocId),1) as [PercentCompletedIn24Hours]

from cte2