您想要做的是在开始计算之前的一天开始时将虚拟传感器状态开关添加到您的设置中。
添加的额外记录是：
0, '2016-04-01 00:00:00'
1, '2016-04-01 00:00:00' -- This is conditional on the first record in your set having a value of 1
整体查询如下
注意：为了确定哪个记录实际上是序列中的第一个记录，我使用了“ID”列。
;WITH Q0 AS(
-- Inserts a new record ( 0, '2016-04-01 00:00:00' ) to the beginning of the day
SELECT TOP 1 0 AS Value, CONVERT( DATETIME, CONVERT( DATE, LogDate )) AS LogDate
FROM #SwitchLog
UNION ALL
-- Inserts a new record ( 1, '2016-04-01 00:00:00' ) to the beginning of the day when the first record has Value = 1
SELECT Value, CONVERT( DATETIME, CONVERT( DATE, LogDate )) AS LogDate
FROM
( SELECT TOP 1 ID, Value, LogDate
FROM #SwitchLog
ORDER BY LogDate ASC, ID ASC ) AS DummyRecord --<-- NOTE: the use of a table ID column
WHERE Value = 1
UNION ALL
SELECT Value, LogDate
FROM #SwitchLog
)
,
Q1 AS (SELECT ROW_NUMBER() OVER (ORDER BY LogDate) AS id,
SUM( Value ) AS Value, LogDate
FROM Q0
GROUP BY LogDate
HAVING COUNT(LogDate) > 1)
SELECT A.Value, DATEDIFF(SECOND,A.LogDate,B.LogDate) AS Total
FROM Q1 AS A
INNER JOIN Q1 AS B
ON B.ID = A.ID + 1 AND B.ID%2 = 0
Output:
Value Total
----------- -----------
1 69
1 1782
1 1782
应使用相同的方法在时间段/天 ((day + 1) 00:00:00) 结束时插入虚拟记录，以满足传感器值在一天结束时为 1 的情况。
