question

Zaran-3086 avatar image
0 Votes"
Zaran-3086 asked LiHongMSFT-3908 commented

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

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.

0 Votes 0 ·
Zaran-3086 avatar image Zaran-3086 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.

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

0 Votes 0 ·

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

195870-image.png


0 Votes 0 ·
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

0 Votes 0 ·

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 Votes 0 ·

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 Votes 0 ·
Zaran-3086 avatar image
0 Votes"
Zaran-3086 answered Zaran-3086 published

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

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

Zaran-3086 avatar image
0 Votes"
Zaran-3086 answered LiHongMSFT-3908 commented

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

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.

196461-image.png

Best regards,
LiHong

0 Votes 0 ·
image.png (19.2 KiB)
Zaran-3086 avatar image
0 Votes"
Zaran-3086 answered Zaran-3086 published

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

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