比较单行中的日期SQL

Question

我目前正在Microsoft SQL server中编写一个查询。我从中获取数据的表格如下：

From_State || To_State || Event_Date
------------------------------------
4          || 8        || 2013-12-10 14:15:55.320
8          || 1        || 2013-12-10 14:29:36.823
1          || 4        || 2013-12-10 14:30:10.230
4          || 8        || 2013-12-10 15:56:08.077
8          || 1        || 2013-12-10 21:03:44.053
1          || 4        || 2013-12-10 21:04:17.470

4=测试，1=空闲，8=离线。

我需要通过比较这些时间戳来获得设备测试当天的百分比。如果不是这一天，我会计算出该设备一天中76%的时间都在测试。

此外，在一天开始的时候可能会有一段时间，这段时间可能会更难把握，因为它与前一天重叠，在结束时也是如此。任何帮助都将不胜感激！

Answer 1

想清楚了，这是我的解决方案：

DECLARE 
      @equipment_id INT = 295, 
      @start_date DATE = '12/01/2013',
      @end_date DATE = '12/11/2013';   
-- Get initial data
WITH data AS 
( 
      -- Ensure rownumber exists to simplify operations
      SELECT ROW_NUMBER() OVER(ORDER BY event_date) AS rownum,
            from_state_id,
            to_state_id,
            event_date
      FROM [triggers].[equipment_state_change] 
      WHERE equipment_id = @equipment_id 
      AND CAST(event_date AS DATE) >= @start_date AND CAST(event_date AS DATE) <= @end_date
), 
-- Determine time differences
data_with_time_differences AS
(
      SELECT 
            thisRecord.rownum, 
            thisRecord.to_state_id,
            thisRecord.event_date,
            CASE WHEN lastRecord.rownum IS NULL OR CAST(lastRecord.event_date AS DATE) < CAST(thisRecord.event_date AS DATE) THEN
                  DATEDIFF(mi, CAST(thisRecord.event_date AS DATE), thisRecord.event_date)
                 WHEN CAST(thisRecord.event_date AS DATE) < CAST(nextRecord.event_date AS DATE) THEN
                  DATEDIFF(mi, thisRecord.event_date, CAST(nextRecord.event_date AS DATE))
            ELSE
                  DATEDIFF(mi, lastRecord.event_date, thisRecord.event_date)
            END AS minutes_in_state,
            -- Mark Records that will be used for % calculation as 4
            CASE WHEN CAST(thisRecord.event_date AS DATE) < CAST(nextRecord.event_date AS DATE) AND thisRecord.to_state_id = 4 THEN
                   4
                 ELSE
                   thisRecord.from_state_id
            END AS recordToCalculateFrom
      FROM data thisRecord
      LEFT JOIN data lastRecord ON lastRecord.rownum = thisRecord.rownum - 1
      LEFT JOIN data nextRecord ON nextRecord.rownum = thisRecord.rownum + 1
)
SELECT SUM(CAST(minutes_in_state AS FLOAT)) /60 /24 * 100 as PercentageOfDayTesting, CAST(event_date AS DATE) as Date
from data_with_time_differences
where recordToCalculateFrom = 4
group by CAST(event_date AS DATE)
order by Date

这得到了我预期的结果。也许这会对外面的人有所帮助。如果不清楚我在这里做什么，请随时询问。