[SOLVED] Filter reports for the last Saturday

Filter reports for the last Saturday - this Friday

I am looking to filter my reports so they gather all the data to Friday.

I.e. today is the 12th and I would like my report to gather info from my date range 5th to the 11th. Then tomorrow on the 13th still get the data from the 5th to the 11th. just a weekly report that comes out on a Friday.

I cant use date diff on the server it doesn't let me on this Oracle I was using this where (date >= trunc(SYSDATE) - 7) but I cant seem to get it to do Friday.

I did a lot of reading and there is a post about levels....... and saw this script. It shows the Friday but I have no idea how to get it to filter on my date column.

WITH dates AS (SELECT TRUNC(SYSDATE, 'mm') + LEVEL -11 dt
               FROM   dual
               CONNECT BY LEVEL <= 22)
SELECT dt,
       to_char(dt, 'fmDay') day_of_dt,
       TRUNC(dt + 3, 'iw') - 3  prev_fri_incl_today,
       TRUNC(dt + 2, 'iw') - 3  prev_fri_not_incl_today
FROM   dates;

Solution

If you want the values from Saturday of the previous week until Friday of the current week (which, if it is currently Monday-Friday will contain future dates) then:

SELECT *
FROM   table_name
WHERE  date_column >= TRUNC(SYSDATE, 'iw') - 2
AND    date_column <  TRUNC(SYSDATE, 'iw') + 5

If you want to include a full historic week (i.e. if it is current Monday-Friday then get last week's range):

SELECT *
FROM   table_name
WHERE  date_column >= TRUNC(SYSDATE - 5, 'iw') - 2
AND    date_column <  TRUNC(SYSDATE - 5, 'iw') + 5

fiddle