How to calculate if workdays are more than 6 days from todays date

Question

I have the following in my select statement:

CASE WHEN DATEADD(DAY, - 5, GETDATE()) > [Date] THEN '6+' ELSE NULL END AS Test

This works fine, but it also includes weekends. How would I go about not counting weekends in the part that is calculating if todays date - 5 is > date? I want to use only 5 working days, not weekends.

So if 5 days back is a weekend, do you want it just to return `NULL`? — JNK, Feb 28 '13 at 21:21
Just change the -5 to -7. Five workdays away -- without including holidays -- is going to be exactly one week. Voila, 7 days. — Gordon Linoff, Feb 28 '13 at 21:23
@GordonLinoff I think he wants 5 calendar days and to exclude if it's a weekend — JNK, Feb 28 '13 at 21:24
See if this helps: http://stackoverflow.com/questions/7444846/business-days-calculation — PM 77-1, Feb 28 '13 at 21:25
@JNK . . . The case of 5 working days is a special case. 5 working days (not counting holidays) is exactly 7 calendar days. The method of changing the 5 to 7 only works, though, when the current date is not on the weekend. And, this would not work for 3 or 8 days. Just 5. — Gordon Linoff, Feb 28 '13 at 21:27
Please just build a calendar table. It will allow you to determine both weekdays/weekends *and* holidays. — Aaron Bertrand, Feb 28 '13 at 21:29

score 1 · Accepted Answer · answered Feb 28 '13 at 21:23

1

This will exclude Saturday and Sunday from your CASE:

CASE WHEN 
    (
       (DATEADD(DAY, - 5, GETDATE()) > [Date]) 
       AND 
       (DATEPART(WEEKDAY, DATEADD(DAY, - 5, GETDATE())) NOT IN (7,1))
    )
    THEN '6+'  END AS Test

answered Feb 28 '13 at 21:23

JNK

63,321
15
122
138

1

This depends on the [DATEFIRST](http://msdn.microsoft.com/en-us/library/ms181598.aspx) setting, yes?. – Magnus Feb 28 '13 at 21:30
Yes sorry, `DateFirst` needs to be 1 – JNK Feb 28 '13 at 21:56

score 0 · Answer 2 · answered Jun 30 '18 at 21:06

I suggest to calculate the workdays from your [Date] to GETDATE() and apply whatever criteria to that number in your CASE expression. To calculate the workdays, for simplicity of expressions, you can add these 2 cross-applies to your FROM clause:

CROSS APPLY ( VALUES (
    DATEDIFF(Day, 0, [Date]),
    DATEDIFF(Day, 0, GETDATE())
    )
) tmp (d1, d2)
CROSS APPLY ( VALUES (
    tmp.d2 - tmp.d1 - (tmp.d2/7 - tmp.d1/7) - ((tmp.d2+1)/7 - (tmp.d1+1)/7)
    )
) diff (workdays)

Having done so, your case expression would look like this:

CASE WHEN diff.workdays > 5 THEN '6+' ELSE NULL END AS Test

How to calculate if workdays are more than 6 days from todays date

2 Answers2