Food for thought:
CREATE TABLE #LOGGING_DATA (
STAFF_MBR varchar(15),
SERVICE_DATE date,
PATIENT varchar(15),
START_TIME time,
END_TIME time,
PATIENT_MINUTES int
);
INSERT INTO #LOGGING_DATA
SELECT 'SUZY STAFF', '06/16/11', 'JOHNNY', '10:00', '10:30', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'DAVID', '10:30', '11:00', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'JACKSON', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'MADDY', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'KEVIN', '11:15', '12:00', 45 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'SARAH', '11:00', '12:00', 60 UNION ALL--overlap
SELECT 'SUZY STAFF', '06/16/11', 'JUDY', '13:00', '13:30', 30 UNION ALL--for staff_mins would count this as 30
SELECT 'SUZY STAFF', '06/16/11', 'JOSEPH', '13:15', '13:45', 30 --overlap AND non-overlap counts as 15
--union all select 'SUZY STAFF', '06/16/11', 'JOSEPH', '23:45', '00:15', 30; -- Uncomment this row to see how it handles spanning a day
go
with
BaseData as (
select
STAFF_MBR,
SERVICE_DATE,
PATIENT,
START_TIME,
END_TIME,
PATIENT_MINUTES,
dt1 = datediff(mi,0,START_TIME),
dt2 = datediff(mi,0,END_TIME) + case when START_TIME < END_TIME THEN 0 ELSE 1440 END
from
#LOGGING_DATA
),
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eNumbers(n) as (select 0 union all
select row_number() over (order by (select null))
from e4 a cross join e2 b)
, UniqueData as (
select distinct
STAFF_MBR,
SERVICE_DATE,
dtn = dt1 + n
from
BaseData bd1
cross apply (select top (PATIENT_MINUTES + 1) n from eNumbers)dt(n)
)
, FinalBaseData as (
select
STAFF_MBR,
SERVICE_DATE,
dtn,
grpdtn = dtn - row_number() over (partition by STAFF_MBR, SERVICE_DATE order by dtn)
from
UniqueData
)
, FinalData as (
select
STAFF_MBR,
SERVICE_DATE,
TimeInMinutes = max(dtn) - min(dtn)
from
FinalBaseData
group by
STAFF_MBR,
SERVICE_DATE,
grpdtn
)
select
STAFF_MBR,
SERVICE_DATE,
TotalTime = sum(TimeInMinutes)
from
FinalData
group by
STAFF_MBR,
SERVICE_DATE
;
go
drop table #LOGGING_DATA;
go