How would I create an SQL Oracle query that searches full 7 day periods starting on Monday and ending on Sundays. It would need to start on the first Monday of each month and, to include the full 7 day period, would need to end on the next Sunday the last day of the month would fall on.
For example:
An August 2024 report would need to be the date range of 08/05/2024 – 09/01/2024.
A September 2024 report would need to be the date range of 09/02/2024 – 10/06/2024
I have this to start with, but it pulls the first and last day of the month. I want to extend it to start on the first Monday and end through to Sunday of the last day.
with
date_range as (
select start_date, end_date, 4 num_weeks_queried
from (
select add_months(trunc(sysdate,'mm'),-1) start_date,
last_day(add_months(trunc(sysdate,'mm'),-1)) end_date from dual
)
You can use:
SELECT CASE
WHEN week_start < month_start
THEN week_start + INTERVAL '7' DAY
ELSE week_start
END AS start_date,
CASE
WHEN week_end < month_end
THEN week_end + INTERVAL '7' DAY
ELSE week_end
END - INTERVAL '1' SECOND AS end_date
FROM (
SELECT TRUNC(SYSDATE, 'MM') AS month_start,
TRUNC(TRUNC(SYSDATE, 'MM'), 'IW') AS week_start,
ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) AS month_end,
TRUNC(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1), 'IW') AS week_end
FROM DUAL
)
Which outputs:
| START_DATE | END_DATE |
|---|---|
| 2024-08-05 00:00:00 | 2024-09-01 23:59:59 |
fiddle