CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mameesh
mameesh

Reputation: 3761

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

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.

Upvotes: 0

Views: 175

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

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

Upvotes: 0

JNK
JNK

Reputation: 65187

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

Upvotes: 1

Related Questions