根据落在范围内的月份开始日期划分为多行输出-解网

问：

我有一个数据，其中给定两个日期，它使用以下查询创建了一系列开始日期和结束日期，间隔为 7 天。

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

查询输出：

现在的要求是，如果当月的结束日期在此范围内，则进一步划分并创建范围，然后从下一个日期开始计算接下来的 7 天，依此类推。因此，理想情况下，下面应该是输出

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

SQL Oracle 公用表表达式日期范围

WITH DateRanges 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
),
RecursiveRanges (WEEK_FIRST_DATE, WEEK_LAST_DATE, SL_NO, NEXT_MONTH_START) AS (
    SELECT
        GREATEST(START_DATE, TRUNC(START_DATE + 7 * (LEVEL - 1))),
        LEAST(END_DATE, TRUNC(START_DATE + 7 * (LEVEL - 1)) + 7 - INTERVAL '1' SECOND),
        LEVEL,
        LEAD(TRUNC(START_DATE + 7 * LEVEL), 1, NULL) OVER (ORDER BY LEVEL) AS NEXT_MONTH_START
    FROM
        DateRanges
    CONNECT BY
        PRIOR WEEK_LAST_DATE + 1 = WEEK_FIRST_DATE
        AND PRIOR SYS_GUID() IS NOT NULL
        AND LEVEL <= CEIL((END_DATE - START_DATE + 1) / 7)
    START WITH
        START_DATE = (SELECT MIN(START_DATE) FROM DateRanges)
)
SELECT
    WEEK_FIRST_DATE,
    CASE
        WHEN WEEK_LAST_DATE > NEXT_MONTH_START - 1 THEN NEXT_MONTH_START - INTERVAL '1' SECOND
        ELSE WEEK_LAST_DATE
    END AS WEEK_LAST_DATE,
    SL_NO
FROM
    RecursiveRanges;

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;

输出：

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

小提琴

根据落在范围内的月份开始日期划分为多行输出

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

评论

评论

评论