使用Oracle SQL拆分跨多月份的起止日期

需求说明 #

需将跨多个月份的起止日期记录拆分为分月的日期区间，示例如下：

• 示例1：原区间 10/02/2023 - 28/02/2023，拆分后为 10/02/2023 - 28/02/2023
• 示例2：原区间 10/02/2023 - 29/08/2023，拆分后为 10/02/2023 - 28/02/2023、01/03/2023 - 31/03/2023、01/04/2023 - 29/08/2023
• 示例3：原区间 01/04/2022 - 31/03/2023，拆分后为 01/04/2022 - 28/02/2023、01/03/2023 - 31/03/2023

原代码问题分析 #

当前代码存在两处核心错误：

1. 月份序列生成逻辑受限：CONNECT BY条件写死仅生成3个月，无法覆盖跨更多月份的场景（如示例2跨7个月、示例3跨12个月）
2. 结束日期判断逻辑错误：CASE条件用固定的add_months(qd.valid_from,2)作为判断基准，完全不符合分月拆分的实际需求

原代码如下：

CASE WHEN qd.valid_from >= TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM') 
     THEN
     TRUNC(qd.valid_from)
     ELSE
     TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')
     END new_start_date,

     CASE WHEN last_day(TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')) >= last_day(TRUNC(add_months(qd.valid_from,2),'MM'))
     THEN
     TRUNC(qd.valid_to)
     ELSE
       
     TRUNC(last_day(TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')))
     
     END new_end_date

   FROM QUOTATIONS_UO QH
   ),
   TABLE(
   CAST(
   MULTISET
   (
   SELECT LEVEL
   FROM dual
   CONNECT BY add_months(TRUNC(qd.valid_from,'MM'),LEVEL - 1) <= add_months(TRUNC(qd.valid_from,'MM'),2)
   ) AS sys.OdciNumberList
  )
 )
)

修正后代码 #

修正后的代码会自动根据起止日期的月份差生成所有需要拆分的月份区间，同时正确计算每个区间的起止日期：

SELECT
    qd.*,
    -- 计算当前拆分区间的起始日期
    CASE
        WHEN TRUNC(qd.valid_from) >= TRUNC(add_months(TRUNC(qd.valid_from, 'MM'), COLUMN_VALUE - 1), 'MM')
            THEN TRUNC(qd.valid_from)
            ELSE TRUNC(add_months(TRUNC(qd.valid_from, 'MM'), COLUMN_VALUE - 1), 'MM')
    END AS new_start_date,
    -- 计算当前拆分区间的结束日期
    CASE
        WHEN LAST_DAY(TRUNC(add_months(TRUNC(qd.valid_from, 'MM'), COLUMN_VALUE - 1), 'MM')) >= TRUNC(qd.valid_to)
            THEN TRUNC(qd.valid_to)
            ELSE LAST_DAY(TRUNC(add_months(TRUNC(qd.valid_from, 'MM'), COLUMN_VALUE - 1), 'MM'))
    END AS new_end_date
FROM QUOTATIONS_UO qd,
     TABLE(
         CAST(
             MULTISET(
                 SELECT LEVEL
                 FROM dual
                 -- 生成从valid_from所在月到valid_to所在月的所有月份序列
                 CONNECT BY add_months(TRUNC(qd.valid_from, 'MM'), LEVEL - 1) <= TRUNC(qd.valid_to, 'MM')
             ) AS sys.OdciNumberList
         )
     )

代码逻辑说明 #

1. 月份序列生成：通过CONNECT BY add_months(TRUNC(qd.valid_from, 'MM'), LEVEL - 1) <= TRUNC(qd.valid_to, 'MM')生成从起始日期所在月到结束日期所在月的所有月份，确保覆盖全部分拆场景
2. 起始日期计算：如果起始日期晚于当前拆分月份的第一天，就用原起始日期；否则用当月第一天
3. 结束日期计算：如果当前拆分月份的最后一天晚于原结束日期，就用原结束日期；否则用当月最后一天

内容的提问来源于stack exchange，提问作者Celine