以时间格式计算备用列中的差值

Question

Status  Date    Time
1   2016-03-16  8:00:00
0   2016-03-16  12:00:00
1   2016-03-16  16:00:00
0   2016-03-16  20:00:00
1   2016-03-16  23:55:00
0   2016-03-17  01:16:00
1   2016-03-17  02:20:00
0   2016-03-17  04:00:00
1   2016-03-17  08:00:00
0   2016-03-17  12:00:00
1   2016-03-17  04:00:00
0   2016-03-17  06:00:00

我有上面提到的表格。我要做的就是计算状态变化的时间之间的差异例如，在前两列中，它应该返回 4 小时的时间差异然后我不想计算第 2 列和第 3 列之间的差异，我想计算第 3 列和第 4 列之间的差异等等......时差应该是时间格式，例如 4 小时或 58 分钟，例如总的来说，当状态从 1 更改为 0 时，我将计算差异请帮忙。

Answer 1

像这样尝试：

编辑：日期和时间到日期时间的组合更改为更简单的方式

DECLARE @tbl TABLE([Status] INT,  [Date] DATE,    [Time] TIME);
INSERT INTO @tbl VALUES
 (1,'2016-03-16','8:00:00')
,(0,'2016-03-16','12:00:00')
,(1,'2016-03-16','16:00:00')
,(0,'2016-03-16','20:00:00')
,(1,'2016-03-16','23:55:00')
,(0,'2016-03-17','01:16:00')
,(1,'2016-03-17','02:20:00')
,(0,'2016-03-17','04:00:00')
,(1,'2016-03-17','08:00:00')
,(0,'2016-03-17','12:00:00')
,(1,'2016-03-17','04:00:00')
,(0,'2016-03-17','06:00:00');
WITH AllStarts AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Date],[Time]) AS RowInx
          ,CAST([date] AS DATETIME)+CAST([time] AS DATETIME) AS TimePoint
    FROM @tbl
    WHERE [Status]=1
)
,AllEnds AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Date],[Time]) AS RowInx
          ,CAST([date] AS DATETIME)+CAST([time] AS DATETIME) AS TimePoint
    FROM @tbl
    WHERE [Status]=0
)
SELECT AllStarts.RowInx
      ,AllStarts.TimePoint AS StartPoint
      ,AllEnds.TimePoint AS [EndPoint]
      ,CAST(AllEnds.TimePoint - AllStarts.TimePoint AS TIME) AS TimeDiff
FROM AllStarts
INNER JOIN AllEnds ON AllStarts.RowInx=AllEnds.RowInx

结果：

1   2016-03-16 08:00:00.000    2016-03-16 12:00:00.000     04:00:00.0000000
2   2016-03-16 16:00:00.000    2016-03-16 20:00:00.000     04:00:00.0000000
3   2016-03-16 23:55:00.000    2016-03-17 01:16:00.000     01:21:00.0000000
4   2016-03-17 02:20:00.000    2016-03-17 04:00:00.000     01:40:00.0000000
5   2016-03-17 04:00:00.000    2016-03-17 06:00:00.000     02:00:00.0000000
6   2016-03-17 08:00:00.000    2016-03-17 12:00:00.000     04:00:00.0000000

Answer 2

SQL Server 2012+ 的简单解决方案，但它可能不适用于 ms-access 2007。

;WITH cte as
(
SELECT  [Status], 
        [Date], 
        [Time], 
        CAST([Date] as datetime) + CAST([Time] As datetime) As [datetime]
FROM MyTable 
WHERE [Date] >= @StartDate
AND [Date] <= @EndDate
)
SELECT  [Status], 
        [Date], 
        [Time], 
        CASE WHEN [Date] = LAG([Date]) OVER (ORDER BY [datetime]) THEN
            CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, LAG([datetime]) OVER (ORDER BY [datetime]), [datetime]), '00:00:00') as time) 
        ELSE 
            NULL
        END
        As TimeDifference
FROM cte 

结果（基于提供的数据）

Status Date       Time             TimeDifference
------ ---------- ---------------- ----------------
1      2016-03-16 08:00:00.0000000 NULL
0      2016-03-16 12:00:00.0000000 04:00:00.0000000
1      2016-03-16 16:00:00.0000000 04:00:00.0000000
0      2016-03-16 20:00:00.0000000 04:00:00.0000000
1      2016-03-16 23:55:00.0000000 03:55:00.0000000
0      2016-03-17 01:16:00.0000000 01:21:00.0000000
1      2016-03-17 02:20:00.0000000 01:04:00.0000000
0      2016-03-17 04:00:00.0000000 01:40:00.0000000
1      2016-03-17 04:00:00.0000000 00:00:00.0000000
0      2016-03-17 06:00:00.0000000 02:00:00.0000000
1      2016-03-17 08:00:00.0000000 02:00:00.0000000
0      2016-03-17 12:00:00.0000000 04:00:00.0000000