SQL Server逾期账龄报表查询重复问题求助

核心问题分析 #

你的查询出现重复数据、账龄金额统计错误的原因如下：

1. 重复数据根源：使用INNER JOIN oinv时，每个账户下的每一张未结清发票都会生成一条独立记录，导致同一账户返回多行重复结果。
2. 账龄统计逻辑错误：子查询SELECT Sum(doctotal) FROM oinv WHERE cardcode = INV.cardcode仅按账户汇总所有发票总额，未过滤对应账龄区间的发票，导致所有符合条件的区间都显示账户总发票额，而非对应区间的实际逾期金额。
3. 无到期余额账户重复：若账户存在多张未结清发票，即使余额为0，也会因多发票关联生成重复行。

修正后的查询语句 #

使用分组汇总+条件聚合替代子查询，直接按账户分组统计各账龄区间金额，彻底避免重复行：

SELECT 
    O.cardcode AS [Account],
    O.cardname AS [Name],
    O.u_creditlimit AS [Credit Limit],
    O.u_onhold AS [On Hold],
    O.balance,
    -- 0-30天逾期金额
    ISNULL(SUM(CASE 
        WHEN DATEDIFF(day, INV.docduedate, GETDATE()) BETWEEN 0 AND 29 
        THEN INV.doctotal 
        ELSE 0 
    END), 0) AS [0 to 30 Days],
    -- 31-60天逾期金额
    ISNULL(SUM(CASE 
        WHEN DATEDIFF(day, INV.docduedate, GETDATE()) BETWEEN 30 AND 59 
        THEN INV.doctotal 
        ELSE 0 
    END), 0) AS [31 to 60 Days],
    -- 61-90天逾期金额
    ISNULL(SUM(CASE 
        WHEN DATEDIFF(day, INV.docduedate, GETDATE()) BETWEEN 60 AND 89 
        THEN INV.doctotal 
        ELSE 0 
    END), 0) AS [61 to 90 Days],
    -- 91-120天逾期金额
    ISNULL(SUM(CASE 
        WHEN DATEDIFF(day, INV.docduedate, GETDATE()) BETWEEN 90 AND 119 
        THEN INV.doctotal 
        ELSE 0 
    END), 0) AS [91 to 120 Days],
    -- 121天以上逾期金额
    ISNULL(SUM(CASE 
        WHEN DATEDIFF(day, INV.docduedate, GETDATE()) >= 120 
        THEN INV.doctotal 
        ELSE 0 
    END), 0) AS [121+ Days]
FROM 
    ocrd O
INNER JOIN 
    oinv INV ON O.cardcode = INV.cardcode
WHERE 
    O.territory = 3
    AND INV.docstatus = 'O'
GROUP BY 
    O.cardcode, O.cardname, O.u_creditlimit, O.u_onhold, O.balance

后续扣除贷项通知单的思路 #

若要扣除贷项通知单（假设对应表为ORIN），可通过左连接贷项通知单表，在聚合时同步扣除对应区间的贷项金额：

1. 左连接ORIN表，关联条件为O.cardcode = ORIN.cardcode，并过滤未结清状态。
2. 在每个账龄区间的SUM(CASE)计算中，减去对应区间的贷项金额总和。

示例片段：

ISNULL(SUM(CASE 
    WHEN DATEDIFF(day, INV.docduedate, GETDATE()) BETWEEN 0 AND 29 
    THEN INV.doctotal 
    ELSE 0 
END) - ISNULL(SUM(CASE 
    WHEN DATEDIFF(day, ORIN.docduedate, GETDATE()) BETWEEN 0 AND 29 
    THEN ORIN.doctotal 
    ELSE 0 
END), 0), 0) AS [0 to 30 Days]

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