时间戳

首页 > 解决方案 > SQL Server - 在某些条件下计算行之间的时间

sql-server - SQL Server - 在某些条件下计算行之间的时间

问题描述


我想计算多行之间的时间基于：

  1. 第一个：名字
  2. 第二：事件类型 - 事件本身有条件，因为计算应适用于 24 小时。

条件：

如果第一个事件是“不可用”，则时间应从 00:00:00 计算到事件发生的时间。否则时间将根据“可用”与下一个“不可用”或“可用”之间的差异计算。如果最后一个活动是“可用”，时间将从活动时间开始计算到 23:59:59

毕竟，计算时间的总和与一些其他信息应该插入到另一个表中。

该表如下所示：

ID 姓名 类型 约会时间
1 X1 无法使用 2021-03-30 04:12:00
2 X1 可用的 2021-03-30 08:12:00
3 X1 可用的 2021-03-30 10:12:00
4 X1 无法使用 2021-03-30 11:32:00
5 X1 可用的 2021-03-30 20:30:00
6 X2 可用的 2021-03-30 08:30:00
7 X2 无法使用 2021-03-30 18:12:00

标签： sql-server

解决方案

这就是我得到的似乎有效的东西。仔细检查是否缺少逻辑。

create table #test (ID int, Name varchar(4),    Type varchar(20),   DateTime datetime)

insert into #test values
('1',   'X1',   'Not Available',    '2021-03-30 04:12:00'),
('2',   'X1',   'Available',    '2021-03-30 08:12:00'),
('3',   'X1',   'Available',    '2021-03-30 10:12:00'),
('4',   'X1',   'Not Available',    '2021-03-30 11:32:00'),
('5',   'X1',   'Available',    '2021-03-30 20:30:00'),
('6',   'X2',   'Available',    '2021-03-30 08:30:00'),
('7',   'X2',   'Not Available',    '2021-03-30 18:12:00')



WITH CTE
 AS (SELECT *
          , Time = CAST(datetime AS TIME(0))
          , NextTime = LEAD(CAST(datetime AS TIME(0))) OVER(
            ORDER BY id)
          , RN = ROW_NUMBER() OVER(PARTITION BY name
            ORDER BY id)
     FROM #test),
 CTE2
 AS (SELECT *
          , first = CASE
                        WHEN RN = 1
                             AND type = 'Not Available' THEN CAST(Time AS TIME(0))
                    END
          , second = CAST(DATEADD(ms, DATEDIFF(second, datetime, LEAD(datetime) OVER(PARTITION BY name
                                      ORDER BY datetime)) * 1000, 0) AS TIME(0))
     FROM cte),
 CTE3
 AS (SELECT id
          , name
          , type
          , datetime
          , TimeDiff = ISNULL(first, second)
     FROM cte2),
 CTE4
 AS (SELECT name
          , TimeSum = CAST(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', TimeDiff)), '00:00:00.000') AS TIME(0))
     FROM cte3
     WHERE type = 'available'
     GROUP BY name)
 SELECT cte3.*
      , cte4.TimeSum
 FROM cte4
      JOIN cte3 ON cte4.name = cte3.name

推荐阅读