I need the time difference between two times in minutes. I am having the start time and end time as shown below:
start time | End Time
11:15:00 | 13:15:00
10:45:00 | 18:59:00
I need the output for first row as 45,60,15 which corresponds to the tine difference between 11:15 and 12:00, 12:00 and 13:00, 13:00 and 13:15 respectively.
Anybody please help out. Thanks in advance.
解决方案
The following works as expected:
SELECT Diff = CASE DATEDIFF(HOUR, StartTime, EndTime)
WHEN 0 THEN CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS VARCHAR(10))
ELSE CAST(60 - DATEPART(MINUTE, StartTime) AS VARCHAR(10)) +
REPLICATE(',60', DATEDIFF(HOUR, StartTime, EndTime) - 1) +
+ ',' + CAST(DATEPART(MINUTE, EndTime) AS VARCHAR(10))
END
FROM (VALUES
(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
(CAST('10:45' AS TIME), CAST('18:59' AS TIME)),
(CAST('10:45' AS TIME), CAST('11:59' AS TIME))
) t (StartTime, EndTime);
To get 24 columns, you could use 24 case statements, something like:
SELECT [0] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 0
THEN DATEDIFF(MINUTE, StartTime, EndTime)
ELSE 60 - DATEPART(MINUTE, StartTime)
END,
[1] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 1
THEN DATEPART(MINUTE, EndTime)
WHEN DATEDIFF(HOUR, StartTime, EndTime) > 1 THEN 60
END,
[2] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 2
THEN DATEPART(MINUTE, EndTime)
WHEN DATEDIFF(HOUR, StartTime, EndTime) > 2 THEN 60
END -- ETC
FROM (VALUES
(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
(CAST('10:45' AS TIME), CAST('18:59' AS TIME)),
(CAST('10:45' AS TIME), CAST('11:59' AS TIME))
) t (StartTime, EndTime);
The following also works, and may end up shorter than repeating the same case statement over and over:
WITH Numbers (Number) AS
( SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1
FROM (VALUES (1), (1), (1), (1), (1), (1)) AS t1 (N)
CROSS JOIN (VALUES (1), (1), (1), (1)) AS t2 (N)
), YourData AS
( SELECT StartTime, EndTime
FROM (VALUES
(CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
(CAST('09:45' AS TIME), CAST('18:59' AS TIME)),
(CAST('10:45' AS TIME), CAST('11:59' AS TIME))
) AS t (StartTime, EndTime)
), PivotData AS
( SELECT t.StartTime,
t.EndTime,
n.Number,
MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR, StartTime, EndTime) = 0 THEN DATEDIFF(MINUTE, StartTime, EndTime)
WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE, StartTime)
WHEN DATEDIFF(HOUR, t.StartTime, t.EndTime) <= n.Number THEN DATEPART(MINUTE, EndTime)
ELSE 60
END
FROM YourData AS t
INNER JOIN Numbers AS n
ON n.Number <= DATEDIFF(HOUR, StartTime, EndTime)
)
SELECT *
FROM PivotData AS d
PIVOT
( MAX(MinuteDiff)
FOR Number IN
( [0], [1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17],
[18], [19], [20], [21], [22], [23]
)
) AS pvt;
It works by joining to a table of 24 numbers, so the case statement doesn't need to be repeated, then rolling these 24 numbers back up into columns using PIVOT