December 22, 2016 at 5:25 am
Hi guys,
I had this task at work and I solved it, but I'd like to know if there is a better solution. I don't want "ready-made food", just a hint, a function I could use instead of my solution which works fine (as far as I know).
In short, this is the task: calculate if the emails are worked/updated (wqi.Status_Updated_On) within 3 working days from the day they have been received (wqi.Created_On), then excluding the week-ends. Emails can be worked on Saturday and Sunday as well.
I used the CASE and the DATEPART function. Monday and Tuesday are quite easy:
SLA = CASE
WHEN DATEPART(dw, wqi.Created_On) = 2 -- 2 stands for Monday, the day the email has been received
AND DATEPART(dw, wqi.Status_Updated_On) IN (2, 3, 4, 5) -- Updated on Monday or the 3 following days
AND DATEPART(ww, wqi.Created_On) = DATEPART(ww, wqi.Status_Updated_On) -- CreatedDate and UpdatedDate are in the same week
THEN 'Within SLA'
The other days of the week are trickier, but I found this solution. This is Friday:
WHEN DATEPART(dw, wqi.Created_On) = 6 -- 6 stands for Friday, the day the email has been received
AND
(
(
DATEPART(dw, wqi.Status_Updated_On) IN (6, 7) -- Updated on Friday or Saturday
AND DATEPART(ww, wqi.Created_On) = DATEPART(ww, wqi.Status_Updated_On) -- CreatedDate and UpdatedDate are in the same week
)
OR
(
DATEPART(dw, wqi.Status_Updated_On) IN (1, 2, 3, 4) -- Updated on Sunday, Monday, Tuesday, Wednesday
AND DATEPART(ww, DATEADD(ww, 1, wqi.Created_On)) = DATEPART(ww, wqi.Status_Updated_On) -- The UpdatedDate falls in the next week
)
)
THEN 'Within SLA'
Any suggestions?
December 22, 2016 at 5:39 am
One alternative is to use a calendar table, with a row for each date and an IsWorkingDay column that you can use to count three working days forward. If All Mondays to Fridays are working days and all Saturdays and Sundays are working days, you might consider that to be overkill. All you need here is a CASE expression to add three days if the Created_On day is Monday or Tuesday, or five days otherwise. If e-mails can also be received at weekends then it gets a little bit more complicated, but not too much.
John
December 28, 2016 at 10:10 am
Yes, the emails can be received every day of the week, including Saturdays and Sundays, but worked from Monday to Saturday. I never used a calendar table, maybe I can implement this solution and then compare it with mine. Anyway, thanks for the quick reply.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply