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:

enter image description here

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