# question

## Calculate Days based on condition

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
``````

· 6

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what does these A, B, C, etc signify?

If Shift is MWFSu, and absence starts on Wed, is it True_C if it ends on next Wed, that is, the same rule as for Monday?

In the output you have a Status column, but you don't explain it?

You probably need to some sort of calendar table. I would think that such a table would have columns M-F, MWFSu etc, with 1 or 0 if the day is included in that shift.

ErlandSommarskog

Hi ErlandSommarskog,

If Shift is MWFSu, and absence starts on Wed, is it True_C if it ends on next Wed, that is, the same rule as for Monday? yes the same rule and Type is True_C

In the output you have a Status column, but you don't explain it? Status colum is the Absence status

You probably need to some sort of calendar table. I would think that such a table would have columns M-F, MWFSu etc, with 1 or 0 if the day is included in that shift.

Zaran-3086

Hi,

Notice that in the expected result set on row 6 you have extra column!

`F15, 8, 10, 2022-03-21, 2022-3-28, MWFSuM, True_C`

Please check and fix the expected result set

image.png (25.3 KiB)

the result should 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,,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

Hi @Zaran-3086
There are two rows for Caseid 'F12' in your result.
I could understand the first row:"F12,8,2022-03-18,2022-03-25,ALL,True_C".However,I cannot understand the second row:"F12,6,2022-03-18,2022-03-22,M-F,True_I". Please explain why AbsenceDate is from 2022-03-18 to 2022-03-22,and why CNT is 6?

Also,i don't understand the logic of the status column.For example,there are two status for Caseid ‘F13’ in your result, 'M-F' and 'All'.Could you explain that?

Best regards,
LiHong

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

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

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Zaran-3086
Thanks for your explanation, i understand the status column now.
However,there is still something in result that I don't quite understand.
1) In the green area,I cannot understand the second row:"F12,6,2022-03-18,2022-03-22,M-F,True_I". Please explain why AbsenceDate is from 2022-03-18 to 2022-03-22,and why CNT is 6?

2) In the red area, which is the Caseid F10, the result is kind weird.

Best regards,
LiHong

image.png (19.2 KiB)

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

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.