SELECT SD.STAFF_ID, SD.SERVICE_DATE, SUM(SD.ELAPSED_MINUTES) AS TTL_TIME
FROM STAFF_DATA AS SD
GROUP BY SD.STAFF_ID, SD.SERVICE_DATE
WHERE NOT EXISTS
	(SELECT 1 FROM STAFF DATA AS S2
	 WHERE S2.STAFF_ID = SD.STAFF_ID
		AND S2.SERVICE_DATE = SD.SERVICE_DATE
		AND S2.BEGIN_TIME < SD.END_TIME
		AND S2.END_TIME >= SD.END_TIME
	)

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

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

with
    BaseData as (
        select
            STAFF_ID,
            SERVICE_DATE,
            CASE_NO,
            STARTTIME,
            ENDTIME,
            ELAPSED_MINS,
            dt1 = datediff(mi,0,STARTTIME),
            dt2 = datediff(mi,0,ENDTIME) + case when STARTTIME < ENDTIME THEN 0 ELSE 1440 END
        from
          ##TIMES --my table
),
    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_ID,
    SERVICE_DATE,
    dtn = dt1 + n
from
    BaseData bd1
    cross apply (select top (ELAPSED_MINS  + 1) n from eNumbers) dt (n)
)
, FinalBaseData as (
select
    STAFF_ID,
    SERVICE_DATE,
    dtn,
    grpdtn = dtn - row_number() over (partition by STAFF_ID, SERVICE_DATE order by dtn)
from
    UniqueData
)
, FinalData as (
select
    STAFF_ID,
    SERVICE_DATE,
     TimeInMinutes = max(dtn) - min(dtn)
from
    FinalBaseData
group by
    STAFF_ID,
    SERVICE_DATE,
    grpdtn
)
select
    STAFF_ID,
    SERVICE_DATE,
    TotalTime = sum(TimeInMinutes)
from
    FinalData
group by
    STAFF_ID,
    SERVICE_DATE
;
go


/*--Not sure if this makes any difference:

Column_name	Type	Computed	Length	Prec	Scale	Nullable	TrimTrailingBlanks	FixedLenNullInSource	Collation
STAFF_ID	int	no	4	10	0	yes	(n/a)	(n/a)	NULL
CASE_NO	char	no	10	NULL	NULL	yes	no	yes	SQL_Latin1_General_CP1_CI_AS
SERVICE_DATE	datetime	no	8	NULL	NULL	yes	(n/a)	(n/a)	NULL
ELAPSED_MINS	int	no	4	10	0	yes	(n/a)	(n/a)	NULL
STARTTIME	time	no	3	8	0	yes	(n/a)	(n/a)	NULL
ENDTIME	time	no	3	8	0	yes	(n/a)	(n/a)	NULL

*/