SQL查询当前债务起始日期及存续天数的实现方法咨询

样例业务数据 #

业务规则 #

• 贷款支持共同借款人还款，若贷款客户未按时偿还当期款项则产生债务，表中会生成Sum为正的未偿金额记录
• 客户后续还款（全额/部分）时会生成Sum为负的还款记录，还款不一定能全额结清累计债务

现有实现代码 #

DROP TABLE IF EXISTS #PDCL
set dateformat dmy
CREATE TABLE #PDCL
(
Payment_dt date,
Customer int,
Deal int,
Currency varchar(5),
Sum_payment int
)
INSERT INTO #PDCL VALUES ('12.12.2009', 111110, 111111, 'RUR', 12000)
INSERT INTO #PDCL VALUES ('25.12.2009', 111110, 111111, 'RUR', 5000)
INSERT INTO #PDCL VALUES ('12.12.2009', 111110, 122222, 'RUR', 10000)
INSERT INTO #PDCL VALUES ('12.01.2010', 111110, 111111, 'RUR', -10100)
INSERT INTO #PDCL VALUES ('20.11.2009', 220000, 222221, 'RUR', 25000)
INSERT INTO #PDCL VALUES ('20.12.2009', 220000, 222221, 'RUR', 20000)
INSERT INTO #PDCL VALUES ('31.12.2009', 220001, 222221, 'RUR', -10000)
INSERT INTO #PDCL VALUES ('29.12.2009', 111110, 122222, 'RUR', -10000)
INSERT INTO #PDCL VALUES ('27.11.2009', 220001, 222221, 'RUR', -30000)

--Start date of the current debt
SELECT Deal
     , MIN(Payment_dt) AS Start_date_current_debt
FROM #PDCL
WHERE Sum_payment > 0
GROUP BY Deal

--Number of days of current debt
SELECT Deal
     , DATEDIFF(d, MIN(Payment_dt), MAX(Payment_dt)) AS Num_days_current_debt
FROM #PDCL
GROUP BY Deal

存在的问题 #

现有语句计算结果不符合需求，需要按Deal维度统计，预期输出如下：

预期输出1：债务起始日期 #

预期输出2：债务存续天数 #

调整方案 #

核心逻辑为：先按时间顺序计算每个Deal的累计余额，找到最后一次余额清零后首次产生正债务的日期，即为当前债务的起始日期。若债务已全部结清，天数为结清日期减起始日期；若仍有未结清债务，天数为当前日期减起始日期。

WITH cte_running_balance AS (
    SELECT 
        Deal,
        Payment_dt,
        Sum_payment,
        SUM(Sum_payment) OVER (PARTITION BY Deal ORDER BY Payment_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
    FROM #PDCL
),
cte_last_zero AS (
    SELECT 
        Deal,
        MAX(CASE WHEN running_total = 0 THEN Payment_dt END) AS last_zero_dt
    FROM cte_running_balance
    GROUP BY Deal
),
cte_current_debt_start AS (
    SELECT 
        r.Deal,
        MIN(r.Payment_dt) AS Start_date_current_debt,
        MAX(CASE WHEN r.running_total = 0 THEN r.Payment_dt END) AS payoff_dt
    FROM cte_running_balance r
    JOIN cte_last_zero z ON r.Deal = z.Deal
    WHERE r.Payment_dt > ISNULL(z.last_zero_dt, '1900-01-01')
        AND r.Sum_payment > 0
    GROUP BY r.Deal
)
-- 输出起始日期
SELECT Deal, Start_date_current_debt FROM cte_current_debt_start;

-- 输出存续天数
SELECT 
    Deal,
    CASE 
        WHEN payoff_dt IS NOT NULL THEN DATEDIFF(d, Start_date_current_debt, payoff_dt)
        ELSE DATEDIFF(d, Start_date_current_debt, GETDATE())
    END AS Num_days_current_debt
FROM cte_current_debt_start;

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