查找特定时间之间时间表数据的差距

问题描述

我正在尝试找出早上 8 点到下午 6 点之间的时间表间隔.我能够找到已记录记录的间隔，但我无法弄清楚如何确定记录是否丢失"——这意味着如果它们在上午 8:30 开始，我无法弄清楚如何识别 30 分钟间隔早上 8 点到 8 点 30 分(即他们开始工作到很晚).

I am trying to find gaps in timesheets between the hours of 8AM and 6PM. I am able to find the gaps for records that are logged, but I cannot figure out how to determine if a record was "missed" - meaning if they started at 8:30 AM, I cannot figure out how to identify the 30 minute gap between 8AM and 8:30 AM (ie. they started work late).

在下面的示例中，我可以找到下午 12 点到 12:30 之间的两个间隔，但不能找到 5/8 的上午 8 点到上午 8:30 的间隔和下午 5:30 到下午 6 点的间隔，以及上午 8:30 的间隔5/10.

In below example, I can find the two gaps between 12 and 12:30 pm, but not the 8am-8:30am gap and 5:30 to 6pm gap on 5/8, and 8am-8:30am gap on 5/10.

有什么想法可以为我指明如何解决这个问题的正确方向?

Any ideas to point me in the right direction on how I could approach this?

drop table #time;
create table #time (
    TimesheetId int not null
    , StartTime datetime not null
    , EndTIme datetime not null
);

insert into #time (TimesheetId, StartTime, EndTime)
    values (210, '2017-05-08 05:30:00.000', '2017-05-08 06:30:00.000')
         , (210, '2017-05-08 06:30:00.000', '2017-05-08 08:30:00.000')
         , (210, '2017-05-08 08:30:00.000', '2017-05-08 12:00:00.000')
         , (210, '2017-05-08 12:30:00.000', '2017-05-08 18:30:00.000')
         , (210, '2017-05-09 08:30:00.000', '2017-05-09 12:00:00.000')
         , (210, '2017-05-09 12:30:00.000', '2017-05-09 17:30:00.000')
         , (210, '2017-05-09 22:30:00.000', '2017-05-10 05:30:00.000')
         , (210, '2017-05-10 08:30:00.000', '2017-05-10 18:00:00.000')
;

; with t1 as (
    SELECT TimesheetId
         , StartTime
         , lag(EndTime) OVER (PARTITION BY TimesheetId ORDER BY StartTime) AS prev_endtime
    FROM #time
    where datepart(HH, StartTime) <= 18
        and datepart(HH, EndTime) >= 8
)
select prev_endtime as gapStart
     , StartTime as gapEnd
from t1
where StartTime <> prev_endtime
and cast(prev_endtime as date) = cast(StartTime as date)
;

推荐答案

WITH
  a AS(SELECT DATEADD(hh, DATEDIFF(dd, 0, StartTime) * 24 + 8, 0) t,
         TimesheetId FROM #time),
  b AS(SELECT * FROM #time UNION ALL SELECT TimesheetId, t, t FROM a UNION ALL
       SELECT TimesheetId, DATEADD(hh, 10, t), DATEADD(hh, 10, t) FROM a),
  c AS(SELECT TimesheetId,
         LAG(EndTime) OVER (
           PARTITION BY TimesheetId ORDER BY StartTime
         ) prev_fin,
         StartTime
       FROM b),
   d AS(SELECT *, DATEADD(hh, DATEDIFF(dd, 0, prev_fin) * 24 + 8, 0) beg,
          DATEADD(hh, DATEDIFF(dd, 0, prev_fin) * 24 + 18, 0) fin
        FROM c)
SELECT TimesheetId, prev_fin, StartTime
FROM d
WHERE prev_fin < StartTime AND
  ((prev_fin >= beg AND prev_fin < fin) OR
   (StartTime > beg AND StartTime <= fin));

在 rextester.com 上查看.

这篇关于查找特定时间之间时间表数据的差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！