Search code examples
sqlsql-serverdatewhere-clausegetdate

WHERE statement to choose record previous day but choose Friday record when current day is Monday Microsoft SQL

I need a WHERE statement where the date of the record is the previous day. I have the below code which will do this 

WHERE DOC_DATE =  dateadd(day,datediff(day,1,GETDATE()),0)

However I need this statement to get Friday's record when the current day is Monday. I have the below code but it will not work for me. No errors come back on SQL although no records results come back either. I have the below code for this

WHERE DOC_DATE = DATEADD(day, CASE WHEN datepart(dw,(GETDATE())) IN (2) then -3 ELSE -1 END ,0)

Important to add that this needs to be in a WHERE clause. This is for a Docuware administrative view I am creating. I have no control on how to write the SELECT statement, I only have access to edit the WHERE clause:

enter image description here

Solution

  • Here's a slightly "magical" way to compute the value that doesn't depend on any particular server settings such as datefirst. It's probably not immediately obvious how it works:

    WHERE DOC_DATE = dateadd(day,datediff(day,'20150316',getdate()),
CASE WHEN DATEPART(weekday,getdate()) = DATEPART(weekday,'20150330')
    THEN '20150313'
    ELSE '20150315' END)

    In the first line, we're computing the number of days which have elapsed since some arbitrary date. I picked a day in March 2015 to use as my base date.

    The second line asks what today's day of the week is and if it's the same as some arbitrary "Known good" Monday. Just taking one value and comparing it to 2 depends on what your DATEFIRST setting is so I prefer not to write that.

    In the third line, we decide what to do if it's a monday - I give a date that is 3 days before my arbitrary date above. If it wasn't a monday, we pick the day before.

    Adding it all together, when we add the days difference from the arbitrary date back to one of these two dates from lines 3 and 4, it has the effect of shifting the date backwards 1 or 3 days.

    It's can be an odd structure to see if you're not familiar with it - but combining dateadd/datediff and exploiting relationships between an arbitrary date and other dates computed from it can be useful for performing all kinds of calculations. A similar structure can be used for computing e.g. the last day of the month 15 months ago using just dateadd/datediff, an arbitrary date and another date with the right offset from the first:

    SELECT DATEADD(month,DATEDIFF(month,'20010101',GETDATE()),'19991031')

    As I said in a comment though, usually doing this sort of thing is only a short step away from needing to properly model your organisation's business days, at which point you'd typically want to introduce a calendar table. At one row per day, 20 years worth of pre-calculated calendar (adjusted as necessary as the business changes) is still less than 10000 rows.