时间戳

首页 > 解决方案 > 从状态更改的日期时间行创建状态日志

sql - 从状态更改的日期时间行创建状态日志

问题描述

我正在将一些数据从远程 API 提取到本地 SQL Server 表，其格式如下。（想象一下它是按 StatusDT 降序排序的）

DriverID     StatusDT                 Status
--------     --------                 ------
b103         2019-03-05 05:42:52:000  D
b103         2019-03-03 23:45:42.000  SB
b103         2019-03-03 21:49:41.000  ON

最终达到我可以返回一个查询的最佳方式是什么，该查询显示每个驱动程序每天在每个状态中花费的总时间量？

此外，状态更新之间可能存在一整天或更长时间的间隙，在这种情况下，我需要一行显示从 00:00:00 到 23:59:59 的每个跳过的先前状态的延续天。所以，如果我在这个表中循环使用下面的结构填充另一个表，上面的例子需要看起来像这样......（再次，按日期降序排序）

DriverID  StartDT              EndDT               Status
--------  ---------------      --------------      ------
b103      2019-03-05 05:42:52                      D
b103      2019-03-05 00:00:00  2019-03-05 05:42:51 SB
b103      2019-03-04 00:00:00  2019-03-04 23:59:59 SB
b103      2019-03-03 23:45:42  2019-03-03 23:59:59 SB
b103      2019-03-03 21:49:41  2019-03-03 23:45:41 ON

那有意义吗？

我最终将 API 数据转储到“工作”表并在其上运行光标以将行添加到另一个表，包括开始和结束日期/时间，但我很好奇是否有另一种可能更有效的方法。

非常感谢。

标签： sqlsql-servertsql

解决方案

我认为这个查询是你需要的。但是，我无法测试它的语法错误：

with x as (
  select
    DriverID,
    StatusDT as StartDT,
    lead(StatusID) over(partition by DriverID order by StatusDT) as EndDT,
    Status
  from my_table
)
select -- start & end on the same day
  DriverID,
  StartDT,
  EndDT,
  Status
from x
where convert(date, StartDT) = convert(date, EndDT) 
   or EndDT is null
union all
select -- start & end on different days; first day up to midnight
  DriverID,
  StartDT,
  dateadd(ms, -3, convert(date, EndDT)) as EndDT,
  Status
from x
where convert(date, StartDT) <> convert(date, EndDT)
  and or EndDT is not null
union all
select -- start & end on different days; next day from midnight
  DriverID,
  convert(date, EndDT) as StartDT,
  EndDT,
  Status
from x
where convert(date, StartDT) <> convert(date, EndDT)
  and or EndDT is not null
order by StartDT desc

推荐阅读