使用递归CTE计算SQL Server中存在间隔的合同到期日期

目标输出 #

SubID,FromDT,ToDT,ContractEndDT,ContractPeriod,MonthTowardExpiration
1,2014-01-01,2014-02-01,2015-01-01, 12, 12
1,2014-02-01,2014-03-01,2015-01-01, 12, 11
1, 2014-03-01, 2014-04-01, 2015-01-01, 12, 10
1, 2014-04-01, 2014-05-01, 2015-01-01, 12, 9
1, 2014-05-01, 2014-06-01, 2015-01-01, 12, 8
1, 2014-06-01, 2014-07-01, 2015-01-01, 12, 7
1, 2014-07-01, 2014-08-01, 2015-01-01, 12, 6
1, 2014-08-01, 2014-09-01, 2015-01-01, 12, 5
1, 2014-09-01, 2014-10-01, 2015-01-01, 12, 4
1, 2014-10-01, 2014-11-01, 2015-01-01, 12, 3
1, 2014-11-01, 2014-12-01, 2015-01-01, 12, 2
1, 2014-12-01, 2015-01-01, 2015-01-01, 12, 1
1, 2015-01-01, 2015-02-01, 2015-01-01, 12, 0
1, 2015-02-01, 2015-03-01, 2015-01-01, 12, -1
1, 2015-03-01, 2015-04-01, 2015-01-01, 12, -2
1, 2015-04-01, 2015-05-01, 2015-01-01, 12, -3
1, 2015-05-01, 2015-06-01, 2015-01-01, 12, -4
1, 2015-06-01, 2015-07-01, 2015-01-01, 12, -5
1, 2015-07-01, 2015-08-01, 2015-01-01, 12, -6
1, 2015-08-01, 2015-09-01, 2017-08-01, 24, 24
1, 2015-09-01, 2015-10-01, 2017-08-01, 24, 23
1, 2015-10-01, 2015-11-01, 2017-08-01, 24, 22
1, 2015-11-01, 2015-12-01, 2017-08-01, 24, 21
1, 2015-12-01, 2016-01-01, 2017-08-01, 24, 20

基础数据表（Contracts） #

SubID, StartDT, EndDT, ContractEndDT, ContractPeriod
1, 2014-01-01, 2015-08-15, 2015-01-01, 12
1, 2015-08-15, 3000-01-01, 2017-08-01, 24

字段说明 #

• SubID：客户ID
• StartDT：记录起始日期
• EndDT：记录结束日期
• ContractEndDT：合同到期日期
• ContractPeriod：合同期限
• MonthTowardExpiration：到期剩余月数（合同期内递减，空档期为负值）

需求描述 #

计算合同的月度状态：

1. 第一个合同从2014-01-01到2015-01-01生成对应行，剩余月数从12递减到1
2. 第一个合同到期后到第二个合同生效前（2015-01-01至2015-08-15）生成空档期行，剩余月数为负值（从0开始递减）
3. 2015-08-15起第二个合同生效，生成剩余月数从24递减的行

当前问题 #

使用递归CTE可以生成两个合同的对应行，但无法生成空档期的负数值行。以下是尝试的代码：

初始代码 #

WITH RecursiveCTE AS (
    -- Anchor member: Starting with the initial row from the Contracts table
    SELECT
        SubID,
        DATEADD(MONTH, -ContractPeriod, ContractEndDT) AS FromDT,
        DATEADD(MONTH, 1, DATEADD(MONTH, -ContractPeriod, ContractEndDT)) AS ToDT,
        ContractEndDT,
        ContractPeriod,
        DATEDIFF(MONTH,StartDT,ContractEndDT) as MonthTowardExpiration 
    FROM Contracts
    WHERE SubID = 1
        AND StartDT = (SELECT MIN(StartDT) FROM Contracts WHERE SubID = 1) 

UNION ALL

-- Recursive member: Generating subsequent rows based on the previous row
SELECT
    SubID,
    DATEADD(MONTH, 1, FromDT) AS FromDT,
    DATEADD(MONTH, 1, DATEADD(MONTH, 1, FromDT)) AS ToDT,
    ContractEndDT,
    ContractPeriod,
    CASE
        WHEN DATEADD(MONTH, 1, FromDT) > ContractEndDT THEN 0
        ELSE MonthTowardExpiration - 1 -- Decrement MonthTowardExpiration
    END AS MonthTowardExpiration
FROM RecursiveCTE
WHERE DATEADD(MONTH, 1, FromDT) <= ContractEndDT
)
SELECT SubID, FromDT, ToDT, ContractEndDT, ContractPeriod, MonthTowardExpiration
--into #temp
FROM RecursiveCTE
ORDER BY FromDT;

调整后代码 #

WITH RecursiveCTE AS (
    -- Anchor member: Starting with the initial row from the Contracts table
    SELECT
        SubID,
        DATEADD(MONTH, -ContractPeriod, ContractEndDT) AS FromDT,
        DATEADD(MONTH, 1, DATEADD(MONTH, -ContractPeriod, ContractEndDT)) AS ToDT,
        ContractEndDT,
        ContractPeriod,
        DATEDIFF(MONTH,StartDT,ContractEndDT) as MonthTowardExpiration 
    FROM Contracts
    WHERE SubID = 1
        AND StartDT = (SELECT MIN(StartDT) FROM Contracts WHERE SubID = 1) 
        OR ContractEndDT = (SELECT MAX(ContractEndDT) FROM Contracts WHERE SubID = 1) 


UNION ALL

-- Recursive member: Generating subsequent rows based on the previous row
SELECT
    SubID,
    DATEADD(MONTH, 1, FromDT) AS FromDT,
    DATEADD(MONTH, 1, DATEADD(MONTH, 1, FromDT)) AS ToDT,
    ContractEndDT,
    ContractPeriod,
    CASE
        WHEN DATEADD(MONTH, 1, FromDT) > ContractEndDT THEN 0
        ELSE MonthTowardExpiration - 1 -- Decrement MonthTowardExpiration
    END AS MonthTowardExpiration
FROM RecursiveCTE
WHERE DATEADD(MONTH, 1, FromDT) <= ContractEndDT
)
SELECT SubID, FromDT, ToDT, ContractEndDT, ContractPeriod, MonthTowardExpiration
--into #temp
FROM RecursiveCTE
ORDER BY FromDT;

解决方案 #

思路是先生成覆盖全时间范围的月度区间序列，再关联合同数据计算剩余月数，包括空档期的负值：

WITH DateRange AS (
    -- 确定整个时间范围的起始和结束点
    SELECT 
        MIN(DATEADD(MONTH, -ContractPeriod, ContractEndDT)) AS StartRange,
        DATEADD(MONTH, ContractPeriod, DATEADD(MONTH, -ContractPeriod, ContractEndDT)) AS EndRange
    FROM Contracts
    WHERE SubID = 1
    UNION ALL
    -- 生成连续的月度区间
    SELECT 
        DATEADD(MONTH, 1, StartRange),
        DATEADD(MONTH, 1, EndRange)
    FROM DateRange
    WHERE DATEADD(MONTH, 1, StartRange) <= (SELECT DATEADD(MONTH, ContractPeriod, DATEADD(MONTH, -ContractPeriod, ContractEndDT)) FROM Contracts WHERE SubID=1 AND ContractEndDT=(SELECT MAX(ContractEndDT) FROM Contracts WHERE SubID=1))
),
-- 获取每个客户的合同信息及上一个合同的到期日
ContractInfo AS (
    SELECT 
        c.SubID,
        c.StartDT,
        c.EndDT,
        c.ContractEndDT,
        c.ContractPeriod,
        LAG(c.ContractEndDT) OVER (PARTITION BY c.SubID ORDER BY c.StartDT) AS PrevContractEndDT
    FROM Contracts c
    WHERE SubID = 1
)
-- 关联月度区间和合同信息，计算剩余月数
SELECT 
    1 AS SubID,
    dr.StartRange AS FromDT,
    dr.EndRange AS ToDT,
    COALESCE(c.ContractEndDT, ci.PrevContractEndDT) AS ContractEndDT,
    COALESCE(c.ContractPeriod, (SELECT ContractPeriod FROM Contracts WHERE SubID=1 AND StartDT=(SELECT MIN(StartDT) FROM Contracts WHERE SubID=1))) AS ContractPeriod,
    CASE
        -- 处于当前合同期内
        WHEN dr.StartRange >= c.StartDT AND dr.StartRange < c.EndDT THEN DATEDIFF(MONTH, dr.StartRange, c.ContractEndDT)
        -- 空档期：使用上一个合同的到期日计算负值
        WHEN c.SubID IS NULL THEN DATEDIFF(MONTH, dr.StartRange, ci.PrevContractEndDT)
        ELSE DATEDIFF(MONTH, dr.StartRange, c.ContractEndDT)
    END AS MonthTowardExpiration
FROM DateRange dr
LEFT JOIN ContractInfo c ON dr.StartRange >= c.StartDT AND dr.StartRange < c.EndDT
CROSS JOIN (SELECT PrevContractEndDT FROM ContractInfo WHERE PrevContractEndDT IS NOT NULL) ci
ORDER BY dr.StartRange;

代码说明 #

1. DateRange CTE：生成从第一个合同起始月到第二个合同结束月的所有月度区间，确保覆盖合同期和空档期。
2. ContractInfo CTE：获取合同信息，并使用LAG()函数获取上一个合同的到期日，用于空档期的负数计算。
3. 主查询：将月度区间与合同信息关联，根据区间所处阶段（合同期/空档期）计算对应的MonthTowardExpiration值，空档期直接用当前区间起始日与上一个合同到期日的月差得到负值。

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