I am trying to calculate interest using window functions on SQL Server 2022
with t as
(
select *
from
(values
('2000-1-1',100,0.1),
('2000-1-2',100,0.1),
('2000-1-3',100,0.1),
('2000-1-4',100,0.1),
('2000-1-5',100,0.1),
('2000-1-6',100,0.1)
) as t(date, amount, perc)
)
select
*,
perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365 interest,
sum(amount) over (order by date rows between unbounded preceding and current row) +
perc * sum(amount) over (order by date rows between unbounded preceding and 1 preceding) / 365
from
t
Can someone point me to a problem why interest calculated isn't being cummulated f.e. I expect balance to be 300.08 on 3rd while it is 300.05 (interest from 2nd isn't taken into account)
1条答案
按热度按时间k4aesqcs1#
As mentioned in the comments, this isn't cumulative aggregation it's recursive aggregation. Effectively your expression wants to read something like:
Amount + PriorBalance + (Interest + (Percentage * PriorBalance) / 365)
To achieve this, you're doing to need to use a recursive CTE, as a cumulative
SUM
(or any of the windowed aggregated) cannot access the prior rows calculated value.Therefore this appears to be the solution you need: