Figures, that you'd need to span dates. I have some code that works for data that's date unique, but perhaps you can expand the time table involved once you see what I was up to. Take a look at the following:

CREATE TABLE #TIMES (

TOD time PRIMARY KEY CLUSTERED

)

;WITH HRS AS (

SELECT TOP (24) RIGHT('0' + CAST(ROW_NUMBER() OVER(ORDER BY number) - 1 AS varchar(2)), 2) AS HR

FROM master.dbo.spt_values

),

MINS AS (

SELECT TOP (60) RIGHT('0' + CAST(ROW_NUMBER() OVER(ORDER BY number) - 1 AS varchar(2)), 2) AS MINS

FROM master.dbo.spt_values

)

INSERT INTO #TIMES

SELECT H.HR + ':' + M.MINS

FROM HRS AS H

CROSS APPLY MINS AS M

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', 15--overlap AND non-overlap counts as 15

;WITH MAX_DATA AS (

SELECT STAFF_MBR, SERVICE_DATE, START_TIME, MAX(END_TIME) AS MAX_END, MAX(PATIENT_MINUTES) AS MAX_MINS

FROM #LOGGING_DATA

GROUP BY STAFF_MBR, SERVICE_DATE, START_TIME

)

SELECT DISTINCT MD.STAFF_MBR, MD.SERVICE_DATE, T.TOD

INTO #INTERVALS

FROM MAX_DATA AS MD

LEFT OUTER JOIN #TIMES AS T

ON MD.START_TIME <= T.TOD

AND MD.MAX_END >= T.TOD

SELECT I1.STAFF_MBR, I1.SERVICE_DATE, COUNT(I1.TOD) / 2 AS PATIENT_MINS

FROM #INTERVALS AS I1

INNER JOIN #INTERVALS AS I2

ON I1.STAFF_MBR = I2.STAFF_MBR

AND I1.SERVICE_DATE = I2.SERVICE_DATE

AND I1.TOD <> I2.TOD

AND ABS(DATEDIFF(ms, I1.TOD, I2.TOD)) = 60000

GROUP BY I1.STAFF_MBR, I1.SERVICE_DATE

ORDER BY I1.STAFF_MBR, I1.SERVICE_DATE

DROP TABLE #LOGGING_DATA

DROP TABLE #TIMES

DROP TABLE #INTERVALS

It uses the Tally table concept to create a table of time values for a 24-hour day, then identifies all the 1 minute intervals that are covered one way or another by a given staff member. Finally, it counts the intervals, needing to divide by 2 as each pair of times that are 1 minute apart is just 1 interval. I added an additional line to the sample data so that I could test it for an overlapping, but not completely overlapping, interval.