在SQL Server中获取重叠时间戳的不同行

debugcn 发表于 Dev

艾伦

我有以下从SQL Server获得的结果集：

employeeNumber | start_date | start_time | end_date     | end_time
---------------+------------+------------+--------------+----------
123            | 10-03-2020 |  18:13:55  |  10-03-2020  | 22:59:46
123            | 10-03-2020 |  18:24:22  |  10-03-2020  | 22:59:51
123            | 10-03-2020 |  23:24:22  |  10-03-2020  | 23:59:51
123            | 11-03-2020 |  18:25:25  |  11-03-2020  | 20:59:51
123            | 12-03-2020 |  18:40:22  |  12-03-2020  | 22:59:52

在某些情况下，我有多行具有与上述相同的重叠时间（行1和2），但是开始和结束时间不同（以秒或分钟为单位的差异）。

虽然我的查询是一个简单的选择查询，可从源表中获取数据，但我可以在where子句中添加什么以获取此类重叠时间戳行的不同行。即对于上面的查询，我希望结果集返回以下内容：

employeeNumber | start_date | start_time | end_date     | end_time    
---------------+------------+------------+--------------+----------
123            | 10-03-2020 |  18:13:55  |  10-03-2020  | 22:59:46
123            | 10-03-2020 |  23:24:22  |  10-03-2020  | 23:59:51
123            | 11-03-2020 |  18:25:25  |  11-03-2020  | 20:59:51
123            | 12-03-2020 |  18:40:22  |  12-03-2020  | 22:59:52

以下是我的查询：

select 
    employeeNumber, start_date, start_time, end_date, end_time
from 
    emp_data
where 
    employeeNumber = 123
order by 
    employeeNumber;

我可能只能提取第一条记录，但是where子句将是什么。

感谢您提供任何帮助，因为我对SQL Server不太熟悉。

戈登·利诺夫

这是复杂的。您需要跟踪“开始”和“结束”。我将假设您的列是datetime或可以组合成单个列的类似内容：

with e as (
      select e.employeeNumber, v.dt, sum(v.inc) as inc,
             sum(sum(v.inc)) over (partition by e.employeeNumber order by v.dt) as in_outs
      from emp_data e cross apply
           (values (start_date + start_time, 1),
                   (end_date + end_time, -1)
           ) v(dt, inc)
      group by e.employeeNumber, v.dt
     )
select employeeNumber, min(dt) as start_datetime, max(dt) as end_datetime
from (select e.*,
             sum(case when in_outs = 0 then 1 else 0 end) over (partition by employeeNumber order by dt) as grp
      from e
     ) e
where in_outs <> 0
group by employeeNumber, grp;

这是db <>小提琴。

这是在做什么

首先将日期/时间转换为日期时间。
然后，这些列将被取消透视并标识为开始和结束，以及+1或-1，以指示当时员工是“进入”还是“现有”。
这些是累积的。
现在您有一个缺口和孤岛的问题，您想在其中找到连续的“英寸”。“岛屿”是使用“ ins”的累积总和来标识的。
然后将这些汇总。

编辑：

您可以将累计金额替换为：

from (select e.*,
             (select sum(case when e2.in_outs = 0 then 1 else 0 end) 
              from e e2
              where e2.employeeNumber = e.employeeNumber
                    e2.dt <= e.dt
             ) as grp
      from e
     ) e

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。