Calculate Days based on condition

Question

question

Zaran-3086 asked Apr 23, '22 LiHongMSFT-3908 commented Apr 26, '22

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

sql-server-transact-sql

· 6

5 |1600 characters needed characters left characters exceeded

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

ErlandSommarskog · Apr 23 at 10:10 PM

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.

0 ·

Zaran-3086 ErlandSommarskog · Apr 23 at 10:37 PM

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.

Thank you for your attention and I'm waiting for your query

0 ·

pituach Zaran-3086 · Apr 24 at 12:27 PM

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

0 ·

image.png (25.3 KiB)

Zaran-3086 · Apr 24 at 02:56 PM

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

0 ·

LiHongMSFT-3908 Zaran-3086 · Apr 25 at 03:05 AM

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

0 ·

Zaran-3086 · Apr 25 at 09:23 AM

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

0 ·

Answer 1 · 2022-04-25T09:16:29.16Z

Zaran-3086 answered Apr 25, '22 Zaran-3086 published Apr 25, '22

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

5 |1600 characters needed characters left characters exceeded

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

Answer 2 · 2022-04-25T09:21:29.367Z

Zaran-3086 answered Apr 25, '22 LiHongMSFT-3908 commented Apr 26, '22

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

· 1

5 |1600 characters needed characters left characters exceeded

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

LiHongMSFT-3908 · Apr 26 at 06:07 AM

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

0 ·

image.png (19.2 KiB)

Answer 3 · 2022-04-25T09:22:17.823Z

Zaran-3086 answered Apr 25, '22 Zaran-3086 published Apr 25, '22

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

5 |1600 characters needed characters left characters exceeded

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

question