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

Question

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

Answer 1

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