Obtain data from Friday, Saturday and Sunday if today is Monday SQL Oracle-CodePudding

I am looking to obtain all data in a table from yesterday in SQL Oracle.

This is simply enough using the WHERE clause, i.e,

SELECT *
FROM My_Data
WHERE TO_DATE(My_Data.Date,'YYYY-MM-DD') = TRUNC(SYSDATE)-1

However if I now need to add more logic where if the day of the query is a Monday (SYSDATE) then obtain data between Friday and Sunday.

Using a between statement is no issue, I'm just not sure if I can include in a where statement given I'm unable to use case statement here.

Thanks

CodePudding user response：

SELECT 
    *
FROM 
    My_Data
WHERE
    TO_DATE(My_Data.Date,'YYYY-MM-DD') 
        Between Case When To_Char(SYSDATE, 'DY') = 'MON' Then TRUNC(SYSDATE)-3 ELSE TRUNC(SYSDATE)-1 END 
        And TRUNC(SYSDATE)-1

You can use the Case expression in Where clause. Regards...

CodePudding user response：

Don't use TO_DATE on a column that is already a date (and if it is a string then don't store dates as strings).

So you are not dependent on the date language session parameter, you can compare the date to the start of the ISO week (which is independent of language) and you can compare on a date range so that Oracle can use an index on your date column:

SELECT *
FROM   My_Data
WHERE  "DATE" <  TRUNC(SYSDATE)
AND    "DATE" >= CASE TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW')
                 WHEN 0 -- Monday
                 THEN TRUNC(SYSDATE) - 3
                 ELSE TRUNC(SYSDATE) - 1
                 END;

or:

SELECT *
FROM   My_Data
WHERE  "DATE" <  TRUNC(SYSDATE)
AND    (  ( TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') = 0 AND "DATE" >= TRUNC(SYSDATE) - 3 )
       OR "DATE" >= TRUNC(SYSDATE) - 1
       );