将日期转换为下一个第 10 分钟

我有一个事件表，每行都有一个Start Time和End Time但我想计算事件在 10 分钟内处于活动状态的秒数。

桌子

StartTime             EndTime                 AssetID   EventID
.........              ........              .......    ........
2014/12/01 10:29:50   2014/12/01 10:32:09     10       10045
2014/12/01 10:29:55   2014/12/01 10:45:10     11       10046
2014/12/01 10:33:12   2014/12/01 11:01:45     10       10047

期望输出：-

     Time              DurationActiveSeconds      AssetID         EventID
.................      ....................      .........        .......   
2014/12/01 10:30:00       10                         10           10045
2014/12/01 10:30:00       5                          11           10046
2014/12/01 10:40:00       129                        10           10045
2014/12/01 10:40:00       408                        10           10047
2014/12/01 10:40:00       600                        11           10046
2014/12/01 10:50:00       600                        10           10047
2014/12/01 10:50:00       310                        11           10046
2014/12/01 11:00:00       600                        10           10047
2014/12/01 11:10:00       105                        10           10047

我想以每个AssetID 10 个间隔的时间戳结束，EventID事件活动期间的持续时间以秒为单位计算，我正在运行一个月，如果事件在上个月开始，计数将从 1 日午夜开始。

我已经构建了一个日历表，每秒都有一个条目，但无法弄清楚如何根据需要对数据进行分组。

编辑的详细信息
如果我获取第一行数据，资产 10 在 10：29：50 停止工作，并在 10：32：09 再次开始工作，我想看到的结果集是两个时间段，间隔为 10 分钟：-10：30 和 10：40，从 600（10 分钟内的秒数）中扣除 10 秒（10：30 周期），从 10：
40 周期的 600 中扣除 129 秒。提供如下表格：-

    EndPeriod        Asset  Event  Duration
   ...........       .....  .....  ........
2014/12/01 10:30:00   10    10045    590
2014/12/01 10:40:00   10    10045    471

以下是更多源数据：-
开始时间、结束时间、资产、事件 ID、持续时间
2014-10-01 10：29：50.000， 2014-10-01 10：32：09.000， Asset01， 710， 138.9999998
2014-10-01 15：26：43.000， 2014-10-01 15：27：30.000， Asset02， 10， 46.99999967
2014-10-01 15：26：44.000， 2014-10-01 15：36：44.000， Asset02， 5760， 599.9999994
2014-10-01 16：33：48.000， 2014-10-01 16：43：49.000， Asset01， 5760， 601.0000003
2014-10-01 16：33：48.000， 2014-10-01 16：34：35.000，资产01， 10， 47.0000003
2014-10-01 16：47：48.000， 2014-10-01 16：48：35.000， Asset02， 10， 46.99999967
2014-10-01 20：55：20.000， 2014-10-01 20：56：07.000，资产02， 10， 47.0000003
2014-10-01 21：47：45.000， 2014-10-01 21：48：33.000， Asset02， 10， 47.99999991
2014-10-01 23：04：12.000， 2014-10-01 23：05：00.000，资产02， 10， 47.99999991
2014-10-02 03：35：36.000， 2014-10-02 03：36：23.000， Asset02， 10， 46.99999967
2014-10-02 04：56：22.000， 2014-10-02 04：57：09.000， Asset01， 10， 46.99999967
2014-10-02 04：57：08.000， 2014-10-02 05：11：49.000， Asset01， 6200， 880.9999997
2014-10-02 05：14：06.000， 2014-10-02 05：29：52.000， Asset02， 6200， 945.9999999
2014-10-02 05：24：40.000， 2014-10-02 05：25：27.000，资产01， 10， 47.0000003
2014-10-02 06：15：06.000， 2014-10-02 06：15：53.000，资产02， 10， 47.0000003
2014-10-02 07：01：00.000， 2014-10-02 07：01：48.000， Asset02， 10， 47.99999991
2014-10-02 11：34：58.000， 2014-10-02 11：35：45.000， Asset02， 10， 46.99999967
2014-10-02 11：42：02.000， 2014-10-02 11：44：21.000， Asset01， 65， 138.9999998
2014-10-02 14：43：35.000， 2014-10-02 14：44：22.000，资产01， 10， 47.0000003
2014-10-02 14：51：12.000， 2014-10-02 14：51：59.000， Asset02， 10， 47.0000003
2014-10-03 12：00：00.000， 2014-10-03 12：00：47.000，资产02， 10， 47.0000003
2014-10-04 11：29：03.000， 2014-10-04 11：32：00.000， Asset01， 68， 176.9999999
2014-10-05 04：51：19.000， 2014-10-06 21：15：33.000， Asset02， 5000， 145454
2014-10-05 08：19：44.000， 2014-10-05 08：20：32.000， Asset02， 10， 48.00000053
2014-10-05 08：26：34.000， 2014-10-05 08：27：21.000， Asset01， 10， 46.99999967

我希望这是有道理的

非常感谢您抽出宝贵时间阅读本文
马克

我想你正在寻找类似的东西

SUM (StartTime BETWEEN [calculated first time] AND [calculated last time]).

这对你有帮助吗？还是我误解了你的问题？

示例表

CREATE TABLE #TEMP(StartTime DATETIME,EndTime DATETIME,AssetID INT,EventID INT)
INSERT INTO #TEMP            
SELECT '2014/12/01 10:29:50',   '2014/12/01 10:32:09',     10 ,      10045
UNION ALL
SELECT '2014/12/01 10:29:55',   '2014/12/01 10:45:10',     11,       10046
UNION ALL
SELECT '2014/12/01 10:33:12',   '2014/12/01 11:01:45',     10,       10047

查询

使用recursive CTE获取介于两者之间的日期。在 CTE 内部，我已经将分钟转换为下一个 10 分钟，即 min-32 转换为 minute-40，分钟-29 转换为 minute-30 等

;WITH CTE AS
( 
     SELECT StartTime,EndTime,AssetID,EventID,
      -- Select the first word from Hour, cast to varchar, append with 0 to get next 10th minute
      MIN(DATEADD(MINUTE,10,CAST(CAST(CAST(StartTime AS DATE)AS VARCHAR(12)) + ' ' +
      CAST(DATEPART(HOUR,StartTime)AS VARCHAR(2)) + ':' +
      CAST(LEFT(RIGHT('0'+LEFT(DATEPART(MINUTE,StartTime),2),2),1) AS VARCHAR(2))+ '0' +':00' AS DATETIME)))NewStartTime,     
      MIN(DATEADD(MINUTE,10,CAST(CAST(CAST(EndTime AS DATE)AS VARCHAR(12)) + ' ' +
      CAST(DATEPART(HOUR,EndTime)AS VARCHAR(2)) + ':' +
      CAST(LEFT(RIGHT('0'+LEFT(DATEPART(MINUTE,EndTime),2),2),1) AS VARCHAR(2))+ '0' +':00' AS DATETIME)))NewEndTime           
      FROM #TEMP
      GROUP BY StartTime,EndTime,AssetID,EventID
)
,CTE2 AS
(
    -- Find the difference in secnods with the 10th minute and orginal time
    SELECT *,
    DATEDIFF(S,StartTime,NewStartTime) StartDiff,
    DATEDIFF(S,DATEADD(MINUTE,-10,NewEndTime),EndTime)EndDiff 
    FROM CTE
)
,CTE3 AS
(
  -- Recursive CTE(loop) to find in-between dates
  SELECT MIN(NewStartTime)StartTime,AssetID,EventID
  FROM CTE2
  GROUP BY AssetID,EventID
  UNION ALL
  SELECT DATEADD(MINUTE,10,C.StartTime),C.AssetID,C.EventID    
  FROM CTE3 C
  JOIN CTE2 T ON C.AssetID=T.AssetID AND C.EventID =T.EventID
  WHERE C.StartTime < NewEndTime
)
SELECT DISTINCT C3.*,
CASE 
  WHEN C3.StartTime=C2.NewStartTime THEN C2.StartDiff
  WHEN C3.StartTime=C2.NewEndTime THEN C2.EndDiff 
  ELSE 600
END AS ACT  
FROM CTE3 C3
LEFT JOIN CTE2 C2 ON (C3.StartTime=C2.NewStartTime OR C3.StartTime=C2.NewEndTime)
AND C2.AssetID=C2.AssetID AND C3.EventID =C2.EventID
OPTION(MAXRECURSION 0)

点击这里查看结果

如果您只想将datetime舍入到下一个 10 分钟间隔，那么您可以使用的公式为：

DATEADD(minute,((DATEDIFF(minute,0,<Value to Round>)/10+1)*10),0)

例如，对于当前时间：

DATEADD(minute,((DATEDIFF(minute,0,CURRENT_TIMESTAMP)/10+1)*10),0)

刚回来给我：

2015-01-16 08:00:00.000

相关内容

最新更新

热门标签：