没有重叠的情况下计算总时间

Question

标准工作时间开始于08:00，结束于16:00，因此我为此声明了两个参数：

DECLARE @StartTime TIME = '08:00',
        @EndTime TIME = '16:00'

所有时间计算都必须在开始和结束时间之间。

表1 工作时间

Id      Date        WorkStartTime     WorkEndTime
-------------------------------------------------------
 1   2019/04/20     08:30            14:20
 2   2019/04/21     09:05            15:15
 3   2019/04/22     08:37            14:22
 4   2019/04/23     08:05            15:00

表格2的叶子

Id      Date       LeaveStartTime   LeaveEndTime    
--------------------------------------------------------
 1   2019/04/20     08:00            09:00      
 2   2019/04/21     08:30            16:00
 3   2019/04/22     07:00            08:45
 4   2019/04/22     14:20            17:00

并且根据表1（工作时间）找出每天没有重叠的总请假时间（我的意思是计算请假时间（来自表2）-工作时间（表1））。

这是我需要的结果：

2019/04/20  30 min 
2019/04/21  80 min -- (35' at first and 45' at end)
2019/04/22  98 min -- (calculate 14:22(workendtime bigger than lavestarttime)   to 16:00 (for @EndTime))

我希望能够在SQL查询中实现此操作。我正在使用SQL Server 2016。

- Alavi

---

请详细说明如何确定“2019/04/21 80分钟”这个数字。 - Squirrel

你为什么要将“2019/04/22”的工作时间考虑到“2019/04/21 80分钟”中呢？ - Squirrel

80分钟不是工作时间，而是休假时间（所有休假时间 - 工作时间）= 80分钟。 - Alavi

我很困惑。你的结果中第二行“2019/04/21 80分钟”，是针对“2019/04/21”的，但为什么在计算中使用了“2019/04/22”的工作时间？这里的逻辑是什么？ - Squirrel

我不明白工作时间和休假时间如何重叠，这是几个人的总和吗？ - Joakim Danielson

它在2019/04/21从08:37到14:22工作，但它请假从08:30到16:00。如果我们从离开时间减少工作时间，那么一天的开始将保持35分钟，最后将保持45分钟，35+45=80。 - Alavi

Answer 1

你的计算不清楚。对于第三行，你指定了98分钟，但这忽略了一天开始时的37分钟。

你可以在SQL Server中进行此计算。重叠的时间段有些混乱，但在SQL中绝对是可行的。

以下是我认为你想要的逻辑：

select l.date,
       sum(datediff(minute, l.LeaveStartTime,
                    (case when l.LeaveStartTime < w.workStartTime then w.workStartTime else l.LeaveStartTime end)
                   ) +
           datediff(minute,
                    (case when l.LeaveEndTime > w.workEndTime then w.workEndTime else l.LeaveEndTime end),
                    l.LeaveEndTime
                   )
          ) as overlaps
from (select l.d, l.date,
             (case when LeaveStartTime < '08:00' then '08:00' else LeaveStartTime end) as LeaveStartTime,
             (case when LeaveEndTime > '16:00' then '16:00' else LeaveEndTime end) as LeaveEndTime
      from leaves l
      where LeaveEndTime > '08:00' and LeaveStartTime < '16:00'
     ) l join
     works w
     on w.date = l.date and
        (l.LeaveStartTime < w.WorkStartTime or
         l.leaveEndTime > w.workEndTime
        )
group by l.date;

这里是一个 db<>fiddle。

这个问题比大多数类似的问题要棘手，因为你不想要有重叠部分，而是想要剩下的部分。这需要考虑工作时间的“之前”和“之后”的情况。

- Gordon Linoff