SQL Server TSQL window function to calculate interest

pkbketx9  于 2023-08-02  发布在  其他
关注(0)|答案(1)|浏览(93)

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)

k4aesqcs

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:

WITH t AS (
    SELECT date,
           amount,
           perc,
           ROW_NUMBER() OVER (ORDER BY date ASC) AS I
    FROM (VALUES(CONVERT(date,'20000101'),100,0.1),
                (CONVERT(date,'20000102'),100,0.1),
                (CONVERT(date,'20000103'),100,0.1),
                (CONVERT(date,'20000104'),100,0.1),
                (CONVERT(date,'20000105'),100,0.1),
                (CONVERT(date,'20000106'),100,0.1)) as t(date,amount,perc)),
rCTE AS (
    SELECT date,
           amount,
           perc,
           I,
           CONVERT(numeric(7,6),0.000000) AS interest,
           CONVERT(numeric(12,6),0.000000) AS balance
    FROM t
    WHERE I = 1
    UNION ALL
    SELECT t.date,
           t.amount,
           t.perc,
           t.I,
           CONVERT(numeric(7,6),r.interest + ((t.perc * r.amount)/365)) AS interest,
           CONVERT(numeric(12,6),t.amount + r.balance + (r.interest + ((t.perc * r.amount)/365))) AS balance
    FROM t t
         JOIN rCTE r ON r.I = t.I -1
)
SELECT date,
       amount,
       perc,
       interest,
       balance
FROM rCTE;
GO

相关问题