针对员工的SQL加班

Question

我正在尝试编写一个代码，该代码将允许员工在加班时有另一个事务处理，即使他之前拒绝了加班事务处理。

Employee_1 Shift: 9:00:00-18:00:00   OT Start Time: 18:00:00

场景1示例：

**Employee**: Employee_1    **Time:** 18:00:00-22:00:00    **Status:**  Rejected

**Employee:** Employee_1    **Time:** 18:00:00-20:00:00            
**Status:** (This should be allowed since previous transaction is rejected)

场景2

**Employee:** Employee_1  **Time:**18:00:00-21:00:00   **Status:** Authorized

**Employee:**Employee_1   **Time:** 18:00:00-20:00:00  **[Status:][1]** (Overlapping not allowed) 



if exists(select 'X'   from Employee_OT_TBL tbl (nolock)            
where   tbl.ot_plan_date = @ot_plan_date          
and @ot_from_datetime < tbl.ot_to_datetime
and @ot_todatetime > tbl.ot_from_datetime 
and status = case when tbl.ot_status = 'REJ' then 0 else 1 end
and status = case when ot.doc_status = 'PEND' then 0 else 1 end ) 

员工OT开始时间OT结束时间状态Employee_1 18:00:00 21:00:00授权Employee_1 18:00:00 20:00:00不允许重叠

员工OT开始时间OT结束时间状态Employee_1 18:00:00 21:00:00已拒绝Employee_1 18:00:00 20:00:00进行授权(由于之前的申请已授权，因此允许此操作

Answer 1

我最近在SQL for overlapping time periods上工作。

请查看参考文档，详细了解解决方案

我将为您推荐以下SQL代码。

您可以删除包含上一列的CASE语句和OverLapping。

但NewStatus字段用例是必需的。它检查是否发生重叠，然后使用SQL Lag function读取前一个数据行的状态

在UPDATE语句中使用之前，请测试查询

select
 *,
 case when
  (ot_start between (lag(ot_start,1) over (partition by employee order by ot_start)) and (lag(ot_end,1) over (partition by employee order by ot_start))) or
  (ot_end between (lag(ot_start,1) over (partition by employee order by ot_start)) and (lag(ot_end,1) over (partition by employee order by ot_start))) or
  (ot_start < (lag(ot_start,1) over (partition by employee order by ot_start)) and ot_end > (lag(ot_end,1) over (partition by employee order by ot_start))) or
  (ot_start >(lag(ot_start,1) over (partition by employee order by ot_start)) and ot_end < (lag(ot_end,1) over (partition by employee order by ot_start)))
 then 
    'yes'
 when (lag(ot_start,1) over (partition by employee order by ot_start)) is null
 then NULL
 else 'no'
 end as [OverLapping with Previous],

 case when
  (ot_start between (lag(ot_start,1) over (partition by employee order by ot_start)) and (lag(ot_end,1) over (partition by employee order by ot_start))) or
  (ot_end between (lag(ot_start,1) over (partition by employee order by ot_start)) and (lag(ot_end,1) over (partition by employee order by ot_start))) or
  (ot_start < (lag(ot_start,1) over (partition by employee order by ot_start)) and ot_end > (lag(ot_end,1) over (partition by employee order by ot_start))) or
  (ot_start >(lag(ot_start,1) over (partition by employee order by ot_start)) and ot_end < (lag(ot_end,1) over (partition by employee order by ot_start)))
 then 
    case when  (lag([Status],1) over (partition by employee order by ot_start)) = 'Rejected' then 'Approved' else 'Rejected' end
 when (lag(ot_start,1) over (partition by employee order by ot_start)) is null
 then [Status]
 else [Status]
 end as [NewStatus]
from overtime 

我只做了一个有限的测试。以下是输出

​

​