Not quite a complete solution, it doesn't handle spanning midnight

WITH Hours(hrStart,hrEnd) AS (

SELECT CAST(hrStart AS TIME),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','00:00')) h(hrStart,hrEnd))

SELECT h.hrStart AS [Hour],

SUM(DATEDIFF(minute,CASE WHEN t.START < h.hrStart THEN h.hrStart ELSE t.START END,

CASE WHEN t.ENDD > h.hrEnd THEN h.hrEnd ELSE t.ENDD END)) AS minutes

FROM Hours h

INNER JOIN @t t ON t.ENDD > h.hrStart AND t.START < h.hrEnd

GROUP BY h.hrStart

ORDER BY h.hrStart;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537