如何用单段SQL(PL)代码实现合同活动的日期填充与状态标记？

需求回顾 #

现有两张业务表：

• CONTACT_TBL（合同主表）：字段包括CONTRACT_ID、BEGIN_DATE、END_DATE、TOT_AMOUNT，存储合同主信息；
• CONTRACT_ACTIVTY（合同明细表）：字段包括CONTRACT_ID、SEQNUM、END_DATE、AMOUNT、COMMENTS，存储合同活动明细。

需要实现：

1. 为每条合同明细填充BEGIN_DATE：
   • 当SEQNUM=1时，取主表对应合同的BEGIN_DATE；
   • 当SEQNUM≥2时，取同合同下上一条明细的END_DATE加1天；
2. 标记当前活跃状态：若SYSDATE落在该行BEGIN_DATE与END_DATE之间，STATUS设为'Active'，否则为空。

实现SQL（Oracle环境） #

SELECT
    ca.CONTRACT_ID,
    ca.SEQNUM,
    -- 填充BEGIN_DATE：优先取主表日期，否则用上一行END_DATE+1天
    COALESCE(
        ct.BEGIN_DATE,
        LAG(ca.END_DATE) OVER (PARTITION BY ca.CONTRACT_ID ORDER BY ca.SEQNUM) + INTERVAL '1' DAY
    ) AS BEGIN_DATE,
    ca.END_DATE,
    ca.AMOUNT,
    -- 判断活跃状态
    CASE
        WHEN SYSDATE BETWEEN 
            COALESCE(
                ct.BEGIN_DATE,
                LAG(ca.END_DATE) OVER (PARTITION BY ca.CONTRACT_ID ORDER BY ca.SEQNUM) + INTERVAL '1' DAY
            ) 
            AND ca.END_DATE THEN 'Active'
        ELSE NULL
    END AS STATUS
FROM CONTRACT_ACTIVTY ca
LEFT JOIN CONTACT_TBL ct 
    ON ca.CONTRACT_ID = ct.CONTRACT_ID 
    AND ca.SEQNUM = 1 -- 仅关联SEQNUM=1的行获取主表起始日期
ORDER BY ca.CONTRACT_ID, ca.SEQNUM;

关键逻辑说明 #

• 主表关联：通过LEFT JOIN仅匹配SEQNUM=1的明细行，精准获取对应合同的起始日期；
• 日期填充：用COALESCE函数优先取主表日期，对于SEQNUM≥2的行，借助LAG窗口函数获取同合同下上一条明细的结束日期，再加1天作为当前行的起始日期；
• 状态判断：通过CASE语句对比系统日期与明细行的起止日期，符合条件则标记为Active。

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