Calculate Days based on condition

Zaran 21 Reputation points
2022-04-23T20:17:33.483+00:00

Hi Everyone,

I have a Test table with sample data and I want a query that covers my condition below. I appreciated it if you can help me on the coding.

  • An absence is True_C it it is at leat 8 calendar days, it is consecutive days(considering non working days), then type=True_C else True_I otherwise it is True_I
  • if the last day of the absence date is Friday and no more ,so don't consider weekend

1-if shift is M-F and absences are M-F non Sat/Sun and absence again on M-Tu, all of thoes should be True_C ( type=True_C)
4-if the shift is MWFSu and the absences MWFSuM , although there are not 8 absence consecutive days but it should be the type=True_C
4-if the shift is MWFSa and the absences FSaMWF , although there are not 8 absence consecutive days but it should be the type=True_C
5- and any shift doesn't have condition above and no 8 consecutive days the type should be True_I 

Create table Test(id int identity(1,1),Caseid nvarchar(10),Shift nvarchar(20),S_AbsenceDate Datetime,E_AbsenceDate DateTime)
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','MWFSa','2022-04-15','2022-04-15')
,values('F10','MWFSa','2022-04-16','2022-04-16')
,values('F10','MWFSa','2022-04-18','2022-04-18')
,values('F10','MWFSa','2022-04-20','2022-04-20')
,values('F11','MWFSa','2022-04-15','2022-04-15')
,values('F11','MWFSa','2022-04-16','2022-04-16')
,values('F11','MWFSa','2022-04-18','2022-04-18')
,values('F11','MWFSa','2022-04-20','2022-04-20')
,values('F11','MWFSa','2022-04-22','2022-04-22')
,values('F12','M-F','2022-03-17','2022-03-17')
,values('F12','M-F','2022-03-18','2022-03-18')
,values('F12','M-F','2022-03-21','2022-03-21')
,values('F12','M-F','2022-03-22','2022-03-22')

Result shoulb be: 

Caseid,CNT,S_AbsenceDate,E_AbsenceDate,status,Type
------------------------------------------------
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,10,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
F17,6,2022-04-15,2022-04-20,MWFSa,True_I
F11,8,2022-04-15,2022,-04,22,MWFSa,True_C
F12,6,2022-03-18,2022-03-22,M-F,True_I
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Zaran 21 Reputation points
    2022-04-25T09:16:29.16+00:00

    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

    0 comments
  2. Zaran 21 Reputation points
    2022-04-25T09:21:29.367+00:00

    Hi LiHongMSFT,

    If the shift is except M-F, MWFSu, then Status is ALL
    When 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

    I posted the correct values for the Test table and the result. Please consider it

    Thank you for your help

  3. Zaran 21 Reputation points
    2022-04-25T09:22:17.823+00:00

    Hi LiHongMSFT,

    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

    0 comments