SQL - Tid per timme

SELECT id,machid,startDate,endDate,IF(HOUR(startDate <> HOUR(endDate),60-MINUTES(startDate),NULL) AS minutesInStartDate,IF(HOUR(startDate <> HOUR(endDate),60-MINUTES(endDate),NULL AS minutesEndDate,type FROM MyTable

create table Hours(hour int);
insert into Hours(hour)
select 0
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16
union all select 17
union all select 18
union all select 19
union all select 20
union all select 21
union all select 22
union all select 23;

    SELECT id, 
           Hours.hour AS Hour,
           CASE WHEN Hours.hour = DATE_PART('hour', startDate) 
                     AND Hours.hour = DATE_PART('hour', enddate) 
                     THEN DATE_PART('minute', enddate)-DATE_PART('minute', startDate)
                     WHEN Hours.hour = DATE_PART('hour', startDate) 
                     THEN 60 - DATE_PART('minute', startDate)
                     WHEN Hours.hour = DATE_PART('hour', enddate) 
                     THEN DATE_PART('minute', enddate)
                     WHEN Hours.hour > DATE_PART('hour', startDate) 
                     AND Hours.hour < DATE_PART('hour', enddate) 
                     THEN 60
                ELSE 0
           END AS Duration
    FROM  Hours
    LEFT JOIN "FanVadDumtAttJagIntePostadeTabellSchemat" ON 1=1

DECLARE @json varchar(max) = 
'{"rows":[
   {"id":"68","machid":"1","startdate":"2018-02-23 12:09:44","enddate":"2018-02-23 12:11:44","type":"1"},
   {"id":"69","machid":"2","startdate":"2018-02-23 12:11:44","enddate":"2018-02-23 12:13:44","type":"1"},
   {"id":"70","machid":"3","startdate":"2018-02-23 23:58:13","enddate":"2018-02-24 00:12:13","type":"1"}
]}';
WITH 
input AS (
SELECT * FROM OPENJSON(@json, '$.rows')
WITH (
id int,
machid int,
startdate datetime2(0),
enddate datetime2(0),
type int
)
),
a3 (col) AS (SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL),
nums (num) AS (SELECT row_number() OVER(ORDER BY (SELECT NULL)) - 1 FROM a3 x CROSS JOIN a3 y CROSS JOIN a3 z),
hours (hour) AS (SELECT cast(TIMEFROMPARTS(num, 0, 0, 0, 0) as time(0)) FROM nums WHERE num < 24),
calculate AS (
SELECT 
	i.*, sh.hour AS starthour, eh.hour AS endhour,
	CAST(i.startdate as date) AS StartingDate,
	CAST(i.enddate as date) AS EndingDate,
	CASE
		WHEN sh.hour = eh.hour THEN DATEDIFF(second, i.startdate, i.enddate)
		ELSE 3600 - DATEDIFF(second, sh.hour, CAST(i.startdate as time(0)))
	END ConsumedStartHour,
	CASE
		WHEN sh.hour != eh.hour THEN DATEDIFF(second, eh.hour, CAST(i.enddate as time(0)))
		ELSE 0
	END ConsumedEndHour
FROM HOURS sh
INNER JOIN input i ON DATEPART(HOUR,i.startdate) = DATEPART(HOUR,sh.hour)
INNER JOIN hours eh ON DATEPART(HOUR,i.enddate) = DATEPART(HOUR,eh.hour)
),
consumtion (machid, date, hour, seconds) AS (
SELECT machid, StartingDate, starthour, ConsumedStartHour FROM calculate
UNION ALL
SELECT machid, EndingDate, endhour, ConsumedEndHour FROM calculate WHERE ConsumedEndHour > 0
)
SELECT 
	machid, date, hour, SUM(seconds) AS SekunderNyttjat, 
	DATEADD(second, 0 - SUM(seconds), CAST('01:00:00' as time(0))) AS OutnyttjadTid
FROM consumtion GROUP BY machid, date, hour

Resultat:
machid	date			hour		SekunderNyttjat		OutnyttjadTid
1		2018-02-23	12:00:00	120				00:58:00
2		2018-02-23	12:00:00	120				00:58:00
3		2018-02-23	23:00:00	107				00:58:13
3		2018-02-24	00:00:00	733				00:47:47