SQL Server How to build zigzag subtraction

isr3a4wc 于 2023-10-15 发布在其他

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

I have interest payment table and I want to calculate this using subtraction by zigzag transaction in SQL at first I need to do this below

sum (interest) = 3641.18
from total interest (3641.18) - interest(998.96),date(2023-07-22) = outstanding(2642.22)
then from outstanding(2642.22) - interest(941.69),date(2023-08-22) = outstanding(1700.53) until reach the bottom, date(2023-10-22) become outstanding = (0)
| acct | date | interest | outstanding |
| ------------ | ------------ | ------------ | ------------ |
| 963 | 2023-07-22 | 998.96 | 2642.22 |
| 963 | 2023-08-22 | 941.69 | 1700.53 |
| 963 | 2223-09-22 | 881.69 | 818.84 |
| 963 | 2223-10-22 | 818.84 | 0 |

I tried this query:

Select fc.acct   
  , date
  , interest
  ,SUM(case when DATEDIFF(yy,fc.date, tb.date) <= tb.tb1_start and DATEDIFF(yy,fc.date, tb.date) <= tb.tb1_end 
    then fcalc.outs_interest - fc.interest  else 0 end) AS oustanding      
from table fc 
INNER JOIN (Select acct, Sum(interest) outs_interest from table where GROUP BY acct)fcalc 
ON fcalc.acct = fc.acct
,dbo.axsp_get_timebands() tb
where fc.acct = 963
GROUP BY fc.acct, fc.date,fc.interest,fcalc.outs_interest

Output, that I got is like this from:

total interest(3641.18) subtract per line
total interest(3641.18) - interest(998.96),(date(2023-07-22)) = 2642.22
total interest(3641.18) - interest(941.69),(date(2023-08-22)) = 2699.49 like that

acct	date	interest	outstanding
963	2023-07-22	998.96	2642.22
963	2023-08-22	941.69	2699.49
963	2023-09-22	881.69	2759.49
963	2023-10-22	818.84	2822.96

What I was expecting? is like below:
| acct | date | interest | outstanding |
| ------------ | ------------ | ------------ | ------------ |
| 963 | 2023-07-22 | 998.96 | 2642.22 |
| 963 | 2023-08-22 | 941.69 | 1700.53 |
| 963 | 2023-09-22 | 881.69 | 818.84 |
| 963 | 2023-10-22 | 818.84 | 0 |

sql-server

来源：https://stackoverflow.com/questions/77131303/how-to-build-zigzag-subtraction

1条答案

按热度按时间

knsnq2tg1#

Not sure what do you mean exactly by "zigzag subtraction", but you can achieve what you want using window function.

sum(interest) over () gives you the total interest which is 3641.18 .

And

sum(interest) over(order by date) gives you the running total of interest order by date

select *, 
       outstanding = sum(interest) over (partition by acct) 
                   - sum(interest) over (partition by acct
                                             order by date)
from   fc
order by date

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to build zigzag subtraction

1条答案

相关问题

热门标签

最新问答