Ich brauche den Zeitunterschied zwischen zwei Zeiten in Minuten. Ich habe die Start- und Endzeit wie folgt:
start time | End Time
11:15:00 | 13:15:00
10:45:00 | 18:59:00
Ich brauche die Ausgabe für die erste Zeile als 45,60,15, was der Zeitdifferenz zwischen 11:15 und 12:00, 12:00 und 13:00, 13:00 und 13:15 entspricht.
Folgendes funktioniert wie erwartet:
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);
Um 24 Spalten zu erhalten, können Sie 24 Case-Ausdrücke verwenden.
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);
Das Folgende funktioniert auch und kann kürzer sein, als immer wieder denselben Fallausdruck zu wiederholen:
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;
Es funktioniert durch Zusammenfügen mit einer Tabelle mit 24 Zahlen, sodass der Fallausdruck nicht wiederholt werden muss. Dann müssen diese 24 Zahlen mithilfe von PIVOT
wieder in Spalten gerollt werden.
Verwenden Sie DateDiff mit MINUTE-Unterschied:
SELECT DATEDIFF(MINUTE, '11:10:10' , '11:20:00') AS MinuteDiff
Frage, die Ihnen helfen kann:
SELECT StartTime, EndTime, DATEDIFF(MINUTE, StartTime , EndTime) AS MinuteDiff
FROM TableName
Sie können DATEDIFF (eine integrierte Funktion) und% (für die Skalenberechnung) und CONCAT verwenden, um das Ergebnis nur zu einer Spalte zu machen
select CONCAT('Month: ',MonthDiff,' Days: ' , DayDiff,' Minutes: ',MinuteDiff,' Seconds: ',SecondDiff) as T from
(SELECT DATEDIFF(MONTH, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 12 as MonthDiff,
DATEDIFF(DAY, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 30 as DayDiff,
DATEDIFF(HOUR, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 24 as HourDiff,
DATEDIFF(MINUTE, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 60 AS MinuteDiff,
DATEDIFF(SECOND, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 60 AS SecondDiff) tbl