基于摊销计划表判定逾期付款的方法及实例解析

Table 1 为摊销计划表（贷款创建时预定义），字段定义如下：

• PN_NUMBER - 贷款编号（Loan ID）
• SEQUENCE - 还款计划顺序
• AMOUNT - 应还款金额
• MATURITY - 到期日（Due Date）

Table 2 为付款记录表（付款人可支付任意金额），字段定义：

• DATE_PAID - 交易日期

数据表 #

Table 1（摊销计划表） #

Table 2（付款记录表） #

需求目标 #

需要找到对应每笔计划还款金额的付款记录的DATE_PAID，以此判定该笔计划还款是否逾期，参考示例如下：

实现方案 #

核心逻辑是按付款时间顺序累计付款金额，匹配摊销计划的累计应还款额，以此确定每笔计划还款完成时的最晚付款日期，再对比到期日判断是否逾期，具体步骤如下：

1. 计算付款记录的累计还款额 #

对同一贷款的付款记录按交易日期升序排序，计算累计付款金额，追踪每一笔付款后的总还款进度：

WITH paid_with_running_total AS (
    SELECT 
        PN_NUMBER,
        DATE_PAID,
        SUM(AMOUNT_PAID) OVER (PARTITION BY PN_NUMBER ORDER BY DATE_PAID) AS running_total
    FROM Table2
)

2. 计算摊销计划的累计应还款额 #

对同一贷款的摊销计划按顺序升序排序，计算累计应还款额，明确每笔计划完成时需要达到的总还款金额：

, schedule_with_running_total AS (
    SELECT 
        PN_NUMBER,
        SEQUENCE,
        AMOUNT AS ScheduledAmount,
        MATURITY AS ScheduledMaturity,
        SUM(AMOUNT) OVER (PARTITION BY PN_NUMBER ORDER BY SEQUENCE) AS running_schedule_total
    FROM Table1
)

3. 匹配金额并判定逾期 #

将两个累计结果关联，找到累计付款额首次覆盖对应计划累计应还款额的付款日期，以此作为该笔计划的完成日期，再对比到期日判定状态：

WITH paid_with_running_total AS (
    SELECT 
        PN_NUMBER,
        DATE_PAID,
        SUM(AMOUNT_PAID) OVER (PARTITION BY PN_NUMBER ORDER BY DATE_PAID) AS running_total
    FROM Table2
),
schedule_with_running_total AS (
    SELECT 
        PN_NUMBER,
        SEQUENCE,
        AMOUNT AS ScheduledAmount,
        MATURITY AS ScheduledMaturity,
        SUM(AMOUNT) OVER (PARTITION BY PN_NUMBER ORDER BY SEQUENCE) AS running_schedule_total
    FROM Table1
)
SELECT 
    s.SEQUENCE AS Sequence,
    s.ScheduledAmount,
    s.ScheduledMaturity,
    CASE 
        WHEN MIN(p.DATE_PAID) <= s.ScheduledMaturity THEN 'Ontime'
        ELSE 'Late'
    END AS Status,
    MIN(p.DATE_PAID) AS DatePaid
FROM schedule_with_running_total s
JOIN paid_with_running_total p 
    ON s.PN_NUMBER = p.PN_NUMBER 
    AND p.running_total >= s.running_schedule_total
GROUP BY s.PN_NUMBER, s.SEQUENCE, s.ScheduledAmount, s.ScheduledMaturity, s.running_schedule_total
ORDER BY s.SEQUENCE;

逻辑补充 #

• 用MIN(p.DATE_PAID)取覆盖计划金额的最晚付款日期，因为可能多笔付款累计才达到计划要求；
• 累计金额的关联确保还款进度按时间顺序匹配，符合实际还款的冲抵规则。

内容的提问来源于stack exchange，提问作者Ian Christian Hundana Pagacita