计算SQL Server中的时间差(以分钟为单位)

SELECT  Diff = CASE DATEDIFF(HOUR,StartTime,EndTimE)
                    WHEN 0 THEN CAST(DATEDIFF(minutE,EndTimE) AS VARCHAR(10))
                    ELSE CAST(60 - DATEPART(minutE,StartTimE) AS VARCHAR(10)) +
                        ReplicatE(',60',DATEDIFF(HOUR,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('11:59' AS TIME))
        ) t (StartTime,EndTimE);

要获得24列,您可以使用24个案例表达式,例如：

SELECT  [0] = CASE WHEN DATEDIFF(HOUR,EndTimE) = 0
                        THEN DATEDIFF(minutE,EndTimE)
                    ELSE 60 - DATEPART(minutE,StartTimE)
                END,[1] = CASE WHEN DATEDIFF(HOUR,EndTimE) = 1 
                        THEN DATEPART(minutE,EndTimE)
                    WHEN DATEDIFF(HOUR,EndTimE) > 1 THEN 60
                END,[2] = CASE WHEN DATEDIFF(HOUR,EndTimE) = 2
                        THEN DATEPART(minutE,EndTimE) > 2 THEN 60
                END -- ETC
FROM    (VALUES 
            (CAST('11:15' AS TIME),EndTimE);

以下内容也有效,并且最终可能会比重复使用相同的case表达式更短：

WITH numbers (number) AS
(   SELECT  ROW_numbER() OVER(ORDER BY t1.N) - 1
    FROM    (VALUES (1),(1),(1)) AS t1 (N)
            CROSS JOIN (VALUES (1),(1)) AS t2 (N)
),YourData AS
(   SELECT  StartTime,EndTime
    FROM    (VALUES 
                (CAST('11:15' AS TIME),(CAST('09: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,EndTimE) = 0 THEN DATEDIFF(minutE,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,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;

它通过连接到24个数字的表来工作,因此不需要重复case表达式,然后使用PIVOT将这24个数字滚动到列中