sql—使用日期列作为终止条件的递归cte对金额列求和

rbpvctlc 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(294)

我有一个包含贷款数据的表，包括开始日期、贷款期限（贷款将持续多少个月）和每月付款。
我正在尝试创建一个递归的cte，它将返回过去12个月内每月支付的金额。

CREATE TABLE [dbo].[loan](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [starting_date] [date] NULL,
    [loan_term] [int] NULL,
    [monthly_payment] [decimal](19, 2) NULL
) ON [PRIMARY]
GO

样本数据：

INSERT INTO [dbo].[loan]
    ([starting_date]
    ,[loan_term]
    ,[monthly_payment])
VALUES
    ('8/1/2020', 52, 1),
    ('6/2/2010', 64, 2500),
    ('9/11/2011', 66, 650),
    ('7/4/2011', 36, 600),
    ('11/19/2014', 36, 450),
    ('4/21/2017', 24, 650),
    ('6/25/2009', 68, 7500),
    ('4/3/2016', 72, 12000),
    ('5/2/2013', 12, 330),
    ('7/11/2011', 32, 331.33),
    ('6/11/2013', 36, 200),
    ('7/11/2014', 34, 250),
    ('8/15/2015', 32, 673),
    ('9/15/2017', 31, 8),
    ('11/6/2019', 64, 65),
    ('10/13/2017', 52, 128)
GO

以下是我尝试过的：

WITH loanCTE as 
(
-- Anchor Member
SELECT monthly_payment, DATEADD(month, loan_term, starting_date) as loan_end_date, starting_date, starting_date as current_month
    FROM loan

UNION ALL
-- Recursive Member
SELECT monthly_payment, loan_end_date, starting_date, DATEADD(month, 1, current_month)
    FROM loanCTE
    WHERE current_month < loan_end_date -- Terminating condition
)

SELECT  YEAR(current_month) as Year, MONTH(current_month) as Month,  SUM(monthly_payment) as total_montly_payments
    FROM loanCTE
    WHERE Current_Month BETWEEN GETDATE()-365 AND GETDATE()
    GROUP BY YEAR(current_month), MONTH(current_month) 
    ORDER BY YEAR(current_month) DESC, MONTH(current_month) DESC;

返回以下内容：

Year    Month   total_montly_payments
2020    8   12001.00
2020    7   12193.00
2020    6   12193.00
2020    5   12193.00
2020    4   12201.00
2020    3   12201.00
2020    2   12201.00
2020    1   12201.00
2019    12  12201.00
2019    11  12201.00
2019    10  12136.00
2019    9   12136.00
2019    8   136.00

我可以说这是错误的，因为2019-08年的月供总额应该是12202.00，2020-08年的月供总额应该是12194.00，但我不确定我会错在哪里。似乎我在递归成员中增加当前月份的方式或者使用最终查询的方式是关闭的，但是我现在被卡住了。任何建议都非常感谢！

sql sql-server tsql common-table-expression recursion

来源：https://stackoverflow.com/questions/63251299/recursive-cte-to-sum-amount-column-using-date-column-as-terminating-condition

1条答案

按热度按时间

hwamh0ep1#

我认为您的查询工作正常，因为您正在筛选最近一年的记录，所以首先筛选当前月份（实际上，由于当前月份的日期值，问题正在发生，我们应该使用月份的第一个日期来获得正确的结果），然后对结果集执行聚合。您可以先合并记录，然后再应用筛选器，如下所示：

;WITH loanCTE as 
(
-- Anchor Member
SELECT monthly_payment, DATEADD(MM, loan_term, starting_date) as loan_end_date, starting_date, starting_date as current_month
    FROM loan
UNION ALL
-- Recursive Member
SELECT monthly_payment, loan_end_date, starting_date, DATEADD(MM, 1, current_month)
    FROM loanCTE
    WHERE current_month < loan_end_date -- Terminating condition
)
,
temp as
(
    SELECT  YEAR(current_month) as [Year], MONTH(current_month) as [Month],  SUM(monthly_payment) as total_montly_payments
    FROM loanCTE
    GROUP BY YEAR(current_month), MONTH(current_month) 
)

SELECT *
FROM temp
WHERE datefromparts(YEAR, MONTH, 1) BETWEEN GETDATE()-365 AND GETDATE()
ORDER BY [Year] DESC, [Month] DESC

此外，201908年的总额将为12136.0，如下所示：
('4/3/2016', 72, 12000), --12000
('9/15/2017', 31, 8), --8
('10/13/2017', 52, 128) --128
请在这里找到小提琴。

赞(0）回复(0）举报 2021-07-24

我来回答

sql—使用日期列作为终止条件的递归cte对金额列求和

1条答案

相关问题

热门标签

最新问答