I have a table with Start and End Date. I want to partition the date range into hours. This is my query and results soo far.
declare @Date table (
RecID int primary key identity,
startDate datetime,
endDate datetime
);
INSERT INTO @Date(startDate,endDate)
VALUES
('2018-06-12 00:03:29.227','2018-06-12 00:17:50.370'),
('2018-06-12 00:00:03.273','2018-06-12 00:03:25.527'),
('2018-06-12 00:17:54.497','2018-06-12 03:21:16.533');
WITH sDate (RecID, StartDate, EndDate) AS
(
SELECT RecID, StartDate,
CASE
WHEN (DATEADD(mi, 60, startdate) <= enddate) THEN
DATEADD(mi, 60, startdate)
ELSE EndDate
END
FROM @Date
UNION ALL
SELECT TT.RecID, DL.EndDate,
CASE
WHEN (DATEADD(mi, 60, DL.enddate) <= TT.enddate) THEN
DATEADD(mi,60, DL.enddate)
ELSE TT.EndDate
END
FROM @Date AS TT
INNER JOIN sDate DL
ON (TT.RecID = DL.RecID)
WHERE (DL.enddate < TT.enddate)
)
SELECT *,ISNULL(datediff(SS,StartDate,EndDate)/(60.0),0) AS dTime FROM sDate
RecID StartDate EndDate dTime
1 2018-06-12 00:03:29.227 2018-06-12 00:17:50.370 14.350000
2 2018-06-12 00:00:03.273 2018-06-12 00:03:25.527 3.366666
3 2018-06-12 00:17:54.497 2018-06-12 01:17:54.497 60.000000
3 2018-06-12 01:17:54.497 2018-06-12 02:17:54.497 60.000000
3 2018-06-12 02:17:54.497 2018-06-12 03:17:54.497 60.000000
3 2018-06-12 03:17:54.497 2018-06-12 03:21:16.533 3.366666
How do I modify my qry to get the desired results below.(note the end dates for RecID 3)
RecID StartDate EndDate dTime
1 2018-06-12 00:03:29.227 2018-06-12 00:17:50.370 14.350000
2 2018-06-12 00:00:03.273 2018-06-12 00:03:25.527 3.366666
3 2018-06-12 00:17:54.497 2018-06-12 01:00:00.000 41.10000
3 2018-06-12 01:00:00.000 2018-06-12 02:00:00.000 60.000000
3 2018-06-12 02:00:00.000 2018-06-12 03:00:00.000 60.000000
3 2018-06-12 03:00:00.000 2018-06-12 03:21:16.533 21.266666