sqloraclecommon-table-expressiondate-range

# Dividing into multiple row output based on month start date falling within range

I have a data where given two dates, it creates a range of start date and end dates with a gap of 7 days using below query.

``````WITH Ranges AS (
SELECT
TO_DATE('2023-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') START_DATE,
TO_DATE('2023-11-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS')   END_DATE
FROM
DUAL
)
SELECT
GREATEST(START_DATE, TRUNC(START_DATE + 7 *(LEVEL - 1)))  WEEK_FIRST_DATE,
LEAST(END_DATE, TRUNC(START_DATE + 7 *(LEVEL - 1)) + 7 - INTERVAL '1' SECOND) WEEK_LAST_DATE,
LEVEL SL_NO
FROM
Ranges
CONNECT BY
START_DATE + 7 * ( LEVEL - 1 ) <= END_DATE
``````

Query Output:

Now the requirement is to further divide and create the ranges if end date of the month falls within this range and then from next date calculate next 7 days and so on. So ideally, below should be the output

``````16-OCT-23 00:00:00  22-OCT-23 23:59:59  1
23-OCT-23 00:00:00  29-OCT-23 23:59:59  2
30-OCT-23 00:00:00  31-OCT-23 23:59:59 3 ---- as month end date falling in the range, so last date changed.
01-NOV-23 00:00:00 07-NOV-23 23:59:59   4 --- Calculate 7 days gap from next date onwards till we reach end date in the query
08-NOV-23 00:00:00  13-NOV-23 23:59:59  5
``````

Solution

• You can use a recursive query and the `LAST_DAY` function:

``````WITH Ranges (start_date, end_date) AS (
SELECT DATE'2023-10-16', DATE '2023-11-13' FROM DUAL
),
weeks (week_first_date, week_last_date, end_date, sl_no) AS (
SELECT TRUNC(start_date),
LEAST(
TRUNC(start_date) + INTERVAL '6 23:59:59' DAY TO SECOND,
LAST_DAY(TRUNC(start_date) + INTERVAL '23:59:59' HOUR TO SECOND),
end_date
),
end_date,
1
FROM   ranges
UNION ALL
SELECT week_last_date + INTERVAL '1' SECOND,
LEAST(
week_last_date + INTERVAL '7' DAY,
LAST_DAY(week_last_date + INTERVAL '1' DAY),
end_date
),
end_date,
sl_no + 1
FROM   weeks
WHERE  week_last_date < end_date
)
SELECT week_first_date,
week_last_date,
sl_no
FROM   weeks;
``````

Which outputs:

WEEK_FIRST_DATE WEEK_LAST_DATE SL_NO
2023-10-16 00:00:00 2023-10-22 23:59:59 1
2023-10-23 00:00:00 2023-10-29 23:59:59 2
2023-10-30 00:00:00 2023-10-31 23:59:59 3
2023-11-01 00:00:00 2023-11-07 23:59:59 4
2023-11-08 00:00:00 2023-11-13 00:00:00 5

fiddle