排班问题，白班晚班问题

转自、参考：https://bbs.csdn.net/topics/398862474

【1】需求

　　

 

【2】解决

（2.1）思路

思路，详细代码见下面（2.2）

（1）（即递归CTE，t1表）根据表中最小时间、和最大时间，构造出这段时间所有的上下班时间点行，包含最小时间前一天 和 最大时间后一天；

（2）把构造的每一行都与实际表中的行笛卡尔积，这样我们的每一行都对应上区间的所有白班夜班情况时间段；在大表上建议修改表连接，已亲测没有问题。这样可以及大幅度减少无效行结果集数量；

from t1 cross join #a t2 优化改成 from t1 join #a t2 on (t2.begintime-t1.endtime<0.5) and (t1.endtime - t2.endtime<0.5)

（3） where 条件，二度精准筛选，提取我们想要的行

（4）select 中的 case when，根据你的需求，做出判断什么情况，应该拿什么值；

（2.2）解决代码

--
 
use tempdb;
if object_id('A') is null
    drop table #a
CREATE TABLE #A(ID int identity(1,1), NNAME VARCHAR(20), begintime DATEtime, endtime DATEtime);
   
INSERT INTO #A 
VALUES('bm1','2021-01-10 20:57:02','2021-01-11 10:08:29')
 ,('bm2','2021-01-08 17:23:17','2021-01-10 11:54:23')
 ,('bm3','2021-01-08 07:23:17','2021-01-08 07:54:23')
 ,('bm4','2021-01-09 19:23:17','2021-01-09 19:54:23')
 
---------------------
declare @endtime datetime
select @endtime = max(endtime) from #a
;with t1 as ( --构造扩展
    select cast(convert(varchar(10),min(begintime)-1,120)+' 07:30:00' as datetime) as begintime   --这里修改过 min(begintime)-1,120)
    ,cast(convert(varchar(10),min(begintime)-1,120)+' 19:30:00' as datetime) as endtime          --这里修改过  min(begintime)-1,120)
    from #a
    union all
    select dateadd(hour,12,begintime),dateadd(hour,12,endtime) from t1 
    where begintime< @endtime
)
 
select t2.*,
case when convert(char(10),t1.begintime,120)!=convert(char(10),t2.begintime,120) and (t2.begintime-t1.begintime>0.5) then t1.begintime  --判断是否是跨天，这里修改过，新增--》and (t2.begintime-t1.begintime>0.5)
     when t2.begintime>t1.begintime then t2.begintime  
     else t1.begintime
     end as new_begintime
,case when convert(char(10),t1.endtime,120)!=convert(char(10),t2.endtime,120) and (t2.endtime-t1.endtime>0.5) then t1.endtime  -- 这里修改过， 新增--》and (t2.endtime-t1.endtime>0.5)
     when t2.endtime>t1.endtime then t1.endtime
     else t2.endtime
     end as new_endtime
,case when convert(varchar,t1.endtime,8)='07:30:00' then '夜班' 
 else '白班' 
 end as remark
from t1
cross join #a t2
where t1.endtime>t2.begintime and t1.endtime - t2.begintime<0.5 --从开始时间判断
or (t1.endtime>t2.endtime and t1.endtime-t2.endtime <0.5) --从结束时间判断
or (t1.endtime > t2.begintime and t1.endtime < t2.endtime and t2.endtime-t1.endtime>0.5) --跨天判断
or (t1.begintime > t2.begintime and t1.begintime < t2.endtime and t2.endtime-t1.begintime>0.5) --跨天判断
order by t2.begintime desc,new_begintime
 
--