Here is my shot at this using Mark-101232 derived table for times.
--use any database which has this numbers table.
declare @t as table (ptr int identity, START time, ENDD time)
insert into @t (START, ENDD)
SELECT '09:30', '10:00'
union all
SELECT '10:00', '11:00'
union all
SELECT '10:30', '11:30'
union all
SELECT '13:30', '15:30'
union all
SELECT '18:30', '19:00'
union all
SELECT '10:00', '10:30'
union all
SELECT '03:00', '08:30'
union all
SELECT '19:00', '19:45'
--uses number table
--http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx
/**************************************
Most of this code is because of expanding the
times in the start and end. If times span more than
1 hour this will generate a new record for this.
*******************************************/
IF OBJECT_ID('tempdb..#LightHour','u') IS NOT NULL
DROP TABLE #LightHour
;WITH ExpandHours
AS
(
SELECT t.ptr
,e.RowNum
,StartTime = CAST(e.StartTime AS TIME)
,EndTime = CAST(e.EndTime AS TIME)
,OriginalStartTime = t.START
,OriginalEndTime = t.ENDD
FROM @t t
CROSS APPLY (
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY t.ptr ORDER BY prev.Number)
,DATEADD(hh, prev.Number, dateadd(hour, datediff(hour, 0, t.START), 0)) AS StartTime
,DATEADD(hh, curr.Number, dateadd(hour, datediff(hour, 0, t.START), 0)) AS EndTime
FROM dbo.Numbers curr
JOIN dbo.Numbers prev
ON curr.Number = prev.Number + 1
WHERE curr.Number <= DATEDIFF(hh, t.START, t.ENDD)
) e
),
Final
AS
(
SELECT ptr
,StartTime = CASE WHEN StartTime < OriginalStartTime
THEN OriginalStartTime
ELSE StartTime
END
,EndTime = CASE WHEN OriginalEndTime < EndTime
THEN OriginalEndTime
ELSE EndTime
END
FROM ExpandHours
UNION
SELECT ptr
,StartTime = (SELECT MAX(c.EndTime) FROM ExpandHours c WHERE i.ptr = c.ptr)
,EndTime = OriginalEndTime
FROM ExpandHours i
)
SELECT ptr
,Start = StartTime
,Endd = EndTime
INTO #LightHour
FROM Final
WHERE StartTime <> EndTime
UNION
--if under 1 hour then include here
SELECT *
FROM @t
WHERE DATEDIFF(mi, START, ENDD) < 60
IF OBJECT_ID('tempdb..#Final','u') IS NOT NULL
DROP TABLE #Final
SELECT LighHour =hrStart
,TotalTimeOn_MI = SUM(CASE WHEN t.Start >= hrStart AND t.Start < hrEnd
THEN DATEDIFF(mi, t.Start, t.Endd)
ELSE 0
END)
--,ptr
INTO #Final
FROM #LightHour t
CROSS APPLY
(
SELECT hrStart = CAST(hrStart AS TIME)
,hrEnd = CAST(hrEnd AS TIME)
FROM (
VALUES ('00:00','01:00'),('01:00','02:00'),('02:00','03:00'),('03:00','04:00'),
('04:00','05:00'),('05:00','06:00'),('06:00','07:00'),('07:00','08:00'),
('08:00','09:00'),('09:00','10:00'),('10:00','11:00'),('11:00','12:00'),
('12:00','13:00'),('13:00','14:00'),('14:00','15:00'),('15:00','16:00'),
('16:00','17:00'),('17:00','18:00'),('18:00','19:00'),('19:00','20:00'),
('20:00','21:00'),('21:00','22:00'),('22:00','23:00'),('23:00','24:00')) h(hrStart,hrEnd)
) h
GROUP BY h.hrStart
-- ,ptr
SELECT *
FROM #Final
WHERE TotalTimeOn_MI > 0