时间戳

首页 > 解决方案 > 生成老化报告

sql - 生成老化报告

问题描述

我需要使用来自tblAccount的以下示例数据生成老化报告。

+---------+----------+---------------+-------------------+---------------------+-------------+-----------+---------+
| Loan ID | Account  |     Name      | Amortization Date | Amortized Principal | Paid Amount | Date Paid | Balance |
+---------+----------+---------------+-------------------+---------------------+-------------+-----------+---------+
|       2 | A0007787 | JIMMY NEUTRON | 3/9/2020          |             3823.53 |     3823.53 | 3/9/2020  |       0 |
|       2 | A0007787 | JIMMY NEUTRON | 4/9/2020          |             3823.53 |         500 | 3/9/2020  | 3323.53 |
|       2 | A0007787 | JIMMY NEUTRON | 5/9/2020          |             3823.53 |           0 | NULL      | 3823.53 |
+---------+----------+---------------+-------------------+---------------------+-------------+-----------+---------+

零 (0) 支付金额和NULL Date Paid 表示尚未支付摊销本金。以下是我想要的特定老化日期的输出。

帐龄 A - 3 月 12 日帐龄已于 3 月 12 日支付 3 月 9 日的时间表

+---------+----------+---------------+------------+------------+---------+---------+-------+-------+-----
+---------+----------+---------------+------------+------------+---------+---------+-------+-------+-------+---------------+
| Loan ID | Account  |     Name      |    Due     |  Payment   | Balance | Current |  30   |  60   |  90   | 120 and Above |
+---------+----------+---------------+------------+------------+---------+---------+-------+-------+-------+---------------+
|       2 | A0007787 | JIMMY NEUTRON |  3,823.53  |  3,823.53  |       0 |    -    |  -    |  -    |  -    |          -    |
+---------+----------+---------------+------------+------------+---------+---------+-------+-------+-------+---------------+

老化 B - 5 月 10 日500.00 已于 4 月 9 日支付 4 月 9 日的时间表

+---------+----------+---------------+------------+----------+------------+------------+-------+-------+-------+-----------------+
| Loan ID | Account  |     Name      |    Due     | Payment  |  Balance   |  Current   |  30   |  60   |  90   |  120 and Above  |
+---------+----------+---------------+------------+----------+------------+------------+-------+-------+-------+-----------------+
|       2 | A0007787 | JIMMY NEUTRON |  3,823.53  |  500.00  |  3,323.53  |  3,323.53  |  -    |  -    |  -    |            -    |
+---------+----------+---------------+------------+----------+------------+------------+-------+-------+-------+-----------------+

帐龄 C - 6 月 10日 4 月 9 日的时间表有余额，5 月 9 日没有付款

时间表

    +---------+----------+---------------+------------+---------+------------+-----------+------------+------------+-------+-----------------+
| Loan ID | Account  |     Name      |    Due     | Payment |  Balance   |  Current  |     30     |     60     |  90   |  120 and Above  |
+---------+----------+---------------+------------+---------+------------+-----------+------------+------------+-------+-----------------+
|       2 | A0007787 | JIMMY NEUTRON |  7,147.06  |     500 |  7,147.06  |      -    |  3,823.53  |  3,323.53  |  -    |            -    |
+---------+----------+---------------+------------+---------+------------+-----------+------------+------------+-------+-----------------+

我在这里查看并尝试了建议的解决方案，制作了 tweeks，但它似乎不适合我想要制作的内容。

目前，我有这种存储过程：

INSERT INTO @Aging (
Loan, ID, Account, Name, AmortizationSchedule,  AmortizedPrincipal, PaidAmount, DatePaid, Balance)
SELECT Loan, ID, Account, Name, AmortizationSchedule,   AmortizedPrincipal, PaidAmount, DatePaid, Balance
 FROM tblAccount

Select DISTINCT Loan, ID, Account, Name, AmortizationSchedule,  AmortizedPrincipal, PaidAmount, DatePaid, Balance
    (case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf  )) < 1 then balance else 0 end) as [Current],
    (case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf  )) between 1 and 30 then balance else 0 end) as [DueTo30],
    (case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf  )) between 31 and 60 then balance else 0 end) as [DueTo60],
    (case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf  )) between 61 and 90 then balance else 0 end) as [DueTo90],
    (case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf  )) between 91 and 120 then balance else 0 end) as [DueTo120],
    (case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf  )) > 120 then balance else 0 end) as [Over120]
from @Aging where balance<>0.00  
order by Account

但它给出了以下结果，

 +---------+----------+---------------+-------------+------------+-------------+-----------+-------+-------------+----+-----------------+
    | Loan ID | Account  |     Name      |     Due     |  Payment   |   Balance   |  Current  |  30   |     60      | 90 |  120 and Above  |
    +---------+----------+---------------+-------------+------------+-------------+-----------+-------+-------------+----+-----------------+
    |       2 | A0007787 | JIMMY NEUTRON |  90,000.00  |  8,647.06  |  14,294.12  |      -    |  -    |  14,294.12  |  0 |               0 |
    +---------+----------+---------------+-------------+------------+-------------+-----------+-------+-------------+----+-----------------+

似乎它在摊销计划的数量上重复了到期、付款和余额的价值，即三个（三月、四月、五月）

希望得到积极的回应。蒂亚！

标签： sqlsql-serverstored-proceduresreport

解决方案

您的查询提供了一次正确的结果group by并被sum使用。我错过了什么吗？

Select DISTINCT [Loan ID], Account, [Name]
,sum(AmortizedPrincipal) Due, sum(PaidAmount) Payment, sum(Balance) Balance,
    sum(case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf)) < 1 then balance else 0 end) as [Current],
    sum(case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf)) between 1 and 30 then balance else 0 end) as [DueTo30],
    sum(case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf)) between 31 and 60 then balance else 0 end) as [DueTo60],
    sum(case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf)) between 61 and 90 then balance else 0 end) as [DueTo90],
    sum(case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf)) between 91 and 120 then balance else 0 end) as [DueTo120],
    sum(case when DATEDIFF(day,convert(date,AmortizationSchedule),convert(date,@DateAsOf)) > 120 then balance else 0 end) as [Over120]
from @Aging where balance<>0.00  
GROUP BY [Loan ID], Account, [Name]
order by Account

请在此处检查 db<>fiddle 。

推荐阅读