My SQL - How to run different WHERE statements on different weekdays, like one for monday and another for friday

I missunderstod this a lot and thanks again for all the help. I eventually found a solution for my case and it didn't even involve anything like IF Monday THEN SELECT I didn't even needed a CASE-statement.

What I had to do was to put different WHERE statements with an OR in between.

I have stated my final code bolow:

SELECT * FROM
  (SELECT 
    t.pay_date
    , t.supp_name
    , t.client AS row_client
    , t.ip_status
    , t.bank_account
    , t.remitt_curr AS remitt_curr
    , t.remitt_id
    , t.apar_id  
    , t.payment_id
  FROM 
    aipheader t
  WHERE 
    DATEPART(DW, GETDATE()) IN (2,3,4) 
    AND 
    t.pay_date   BETWEEN '2019-01-01' AND DATEADD(DAY,-4,GETDATE())
  OR 
    DATEPART(DW, GETDATE()) IN (5,6) 
    AND 
    t.pay_date   BETWEEN '2019-01-01' AND DATEADD(DAY,-2,GETDATE())) x
ORDER BY 1

The statement above will execute the OR-block that is true, in my case the one with the rigt weekday.

********* OLD QUESTIONS BELOW ***********

I want to run different SELECT statements on different days off the week.

I can actually get the following code to work:

select 
   case 
      when DATEPART(DW, GETDATE()) = 4 then 
         (select 'HELLO' ) 
      ELSE 
         (select 'GOODBYE') 
   end

On a wendsday the code above returns "HELLO", every other workday it would returns "Goodbye". So far so good!

As long as the select statement only return one value it seams to work, but I realy want a full table like below.

The only differance here should be the [* FROM table] part, and it breaks it all:

select 
   case 
      when DATEPART(DW, GETDATE()) = 4 then 
         (select * FROM table1) 
      ELSE 
         (select * FROM table2) 
   end

If I can't even make the above to work I can not make different SELECTs for different workdays, so this is the hard ting to figure out.

I have tried to encapsulate things with () and add a few SELECTs in different ways but it do not work, so it is probably some SQL principal.

I get an error that I think says that this is ok if it is only one value that's returned:

SqlState 37000 Native 116 [Microsoft][ODBC SQL Server Driver][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

THANKS FOR ALL OF THE REPLYS I NOW UNDERSTAND IT A BIT BETTER.

I was not looking to add a column, I tought I could make a complete new SELECT statement. I now see that my CASE statement only adds a column, as you point out.

So then my first ide might be a better aproce, to actually modify the WHERE-clause in the select statement.

I have now tried that but it does not work.

SELECT * FROM aipheader t
   WHERE 
     CASE 
         WHEN DATEPART(DW, GETDATE())  = 4 THEN 
           t.pay_date = '2019-10-15'
         ELSE
           t.pay_date = '2019-10-17'
      END

It returns the error:

SqlState 37000 Native 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.

I have tried the simpler:

SELECT * FROM aipheader t
   WHERE 
     t.pay_date BETWEEN DATEADD(DAY,-120,GETDATE()) AND DATEADD(DAY,-5,GETDATE())

The above is a simpler form, but that one returns the right values. But the one obove that one with the CASE-clause return the error about "="-sign.

Maybee the CASE part in a WHERE statement do not work.

Solution

After your edit, the correct way to filter your date would be the following:

SELECT 
    * 
FROM 
    aipheader t
WHERE
    t.pay_date = CASE WHEN DATEPART(DW, GETDATE()) = 4 THEN '2019-10-15' ELSE '2019-10-17' END

However, do you really want to hard-code the 2019-10-15 and 2019-10-17 dates? Seems like these should be computed automatically as time goes.

Be careful when using DATEPART with DW, since the week number starting point can actually change depending on the server's and/or current session settings. Check this example:

DECLARE @TestDate DATE = '2020-01-01' -- Wednesday

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

SELECT DATEPART(DW, @TestDate) -- Returns 3

SET DATEFIRST 7 -- 1: Sunday, 7: Saturday

SELECT DATEPART(DW, @TestDate) -- Returns 4!

So whenever checking for a particular day of the week, make sure to force the DATEFIRST session parameter to a particular value so it's consistent with your checks.