WHERE clause determined at runtime, can this be done with CASE without dynamic SQL?-CodePudding

I'm trying to create a single query that runs every day of the week, but need to change the WHERE clause condition on one day of the week. On every day except Monday the clause should be:

-- If these were two queries they would look like this.
-- On everyday except Monday
Select * FROM Orders
WHERE Order_Date = DATEADD(dd,-1,GETDATE()) -- yesterday's orders

-- On Monday (where DATEPART(dw,GETDATE()) returns 2 - find all records from the previous week
Select * FROM Orders
WHERE Order_Date >= DATEADD (DATEADD(dd,-7, GETDATE()
OR
Order_Date <= DATEADD (DATEADD(dd,-1, GETDATE()

-- On paper this should work, but doesn't:
Select * From Orders 
WHERE
 CASE 
 WHEN DATEPART(dw,GETDATE()) = 2 THEN Order_Date >= (DATEADD(dd,-7,GETDATE())) AND Order_Date <= (DATEADD(dd,-1,GETDATE())
 ELSE Order_Date = DATEADD(dd,-1,GETDATE())
 END;

Error message is:

Incorrect syntax near '>'.

Is what I'm trying to do which is essentially 'get CASE to return the SQL code I need'. Is this even possible without dynamic SQL? This has to work in system that doesn't allow EXEC.

CodePudding user response：

Extremely easy without using dynamic SQL, just use AND/OR logic.

SELECT *
FROM Orders
WHERE (
    -- Not Monday
    DATEPART(dw,GETDATE()) <> 2
    -- AND Yesterday
    AND Order_Date = CAST(DATEADD(dd,-1,GETDATE()) AS DATE)
)
OR (
    -- Is Monday
    DATEPART(dw,GETDATE()) = 2
    -- AND Last week (note you need AND not OR when combining you week restrictions)
    AND Order_Date >= CAST(DATEADD(dd,-7, GETDATE()) AS DATE)
    AND Order_Date <= CAST(DATEADD(dd,-1, GETDATE()) AS DATE)
)

Note you need to cast your date conditions to a date, else your end up with a time component which will mess with your comparisons. Thats assuming you only store the date for your Order_Date if you also store the time this logic would need to change to handle that.

CodePudding user response：

You can take control of the DATEFIRST setting (and set it back when you're done) so that you can determine a Monday without worrying about regional/language settings interfering. Then you can just use a CASE expression in the lower bound of the range, and the day as the end of the range:

DECLARE @day date = GETDATE(), 
        @df  int  = @@DATEFIRST;

SET DATEFIRST 7;
DECLARE @dw tinyint = DATEPART(WEEKDAY, @day);
SET DATEFIRST @df;

SELECT Order_ID, Order_Date 
  FROM dbo.Orders
  WHERE Order_Date >= DATEADD(DAY, CASE @dw WHEN 2 THEN -7 ELSE -1 END, @day)
    AND Order_Date < @day;

Easy to test different days by assigning a different value to @day; several examples here: db<>fiddle

Another idea if you don't want to mess with @@DATEFIRST but you can rely on a specific language is to just test the DATENAME of the date in question, and follow the same CASE pattern:

DECLARE @day date = GETDATE();

SELECT Order_ID, Order_Date 
  FROM dbo.Orders
  WHERE Order_Date >= DATEADD(DAY, CASE DATENAME(WEEKDAY, @day)
    WHEN 'Monday' THEN -7 ELSE -1 END, @day)
    AND Order_Date < @day;

(If you can rely on a single language and it's not English, just change it to your language's equivalent of Monday.)

Again, easy to test for multiple days; multiple examples here: db<>fiddle

Lots of date/time advice here, including why we should always avoid shorthand like dd and dw:

Dating Responsibly