Hi LiHongMSFT,
If shift is M-F, Then M-F
When shift is MWFSu, and the absence Date is in Monday, Wednesday, Friday, Sunday and Monday then Status is MWFSuM
IWhen shift is MWFSu, and absenceDate is Monday, Wednesday, Friday, Sunday, Monday and Wednesday then Status is MWFSuW
Else shif is ALL
Please consider these values for Test table:
Insert into Test
values('F12','A','2022-03-7','2022-03-8')
,values('F12','B','2022-03-11','2022-03-11')
,values('F12','B','2022-03-14','2022-03-14')
,values('F12','C','2022-03-15','2022-03-15')
,values('F12','D','2022-03-18','2022-03-18')
,values('F12','B','2022-03-19','2022-03-19')
,values('F12','B','2022-03-20','2022-03-20')
,values('F12','A','2022-03-21','2022-03-21')
,values('F12','G','2022-03-22','2022-03-22')
,values('F12','A','2022-03-23','2022-03-23')
,values('F12','A','2022-03-24','2022-03-24')
,values('F12','F','2022-03-25','2022-03-25')
,values('F13','M-F','2022-03-14','2022-03-18')
,values('F13','M-F','2022-03-21','2022-03-22')
,values('F13','M-F','2022-03-24','2022-03-25')
,values('F13','M-F','2022-03-28','2022-03-31')
,values('F15','MWFSu','2022-03-21','2022-03-21')
,values('F15','MWFSu','2022-03-23','2022-03-23')
,values('F15','MWFSu','2022-03-25','2022-03-25')
,values('F15','MWFSu','2022-03-27','2022-03-27')
,values('F15','MWFSu','2022-03-28','2022-03-28')
,values('F15','M-F','2021-03-10','2021-03-15)
,values('F16','MWFSu','2022-03-7','2022-03-7')
,values('F16','MWFSu','2022-03-9','2022-03-9')
,values('F16','MWFSu','2022-03-11','2022-03-11')
,values('F16','MWFSu','2022-03-13','2022-03-13')
,values('F16','MWFSu','2022-03-14','2022-03-14')
,values('F16','MWFSu','2022-03-16','2022-03-16')
,values('F15','M-F','2022-03-15','2022-03-15')
,values('F15','M-F','2022-03-16','2022-03-16')
,values('F10','M-F','2022-03-17','2022-03-17')
,values('F10','M-F','2022-03-18','2022-03-18')
,values('F10','M-F','2022-03-21','2022-03-21')
,values('F10','M-F','2022-03-22','2022-03-22')
Result should be:
F12,8,2022-03-18,2022-03-25,ALL,True_C
F13,9,2022-03-14,2022-03-22,M-F,True_C
F13,4,2022-03-28,2022-03-31,ALL,True_I
F15,8,,2022-03-21,2022-3-28,MWFSuM,True_C
F15,3,2021-03-10,2021-03-15,M-F,True_I
F16,8,2022-03-07,2022-03-16,WFSuMW,True_C
F15,2,2022-03-15,2022-03-16,ALL,True_I
F10,4,2022-03-18,2022-03-15,2022-03-18,True_I
F12,6,2022-03-18,2022-03-22,M-F,True_I