SQL running total up to X-CodePudding

SQL 2019

I have a table that has 5 rows in it, with amounts of 100, 125, 150, 160, 170

I want to return each row that it takes to get a running total of up to 235, and the partial of it.

base table:

invoice ABC, amount 100

invoice DEF, amount 125

invoice GHI, amount 150

invoice JKL, amount 160

invoice MNO, amount 170

I want it to return:

ABC, amount 100

DEF, amount 125

GHI, amount 10

since those are the only 3 lines to get it to the requested 235. I am thinking of something like a user defined function that returns a table, but i can't seem to come up with anything that will handle the running total with the partial on the last one.

Any thoughts? Thanks in advance.

CodePudding user response：

Ok, so the reason behind the summing the invoices remain unclear to me from your post, however here is my solution.

First, lets create a sample table with the 5 invoice lines.

create table dbo.invoices
(
    invoice char(10),
    amount decimal(10,2)
);

insert into dbo.invoices
values ('ABC', 100), ('DEF', 125), ('GHI', 150), ('JKL', 160), ('MNO', 170);

Using windowing functions we can check if we have achieved the target amount:

declare @target_amount decimal(10,2) = 235;

with input as (
select invoice
, amount
, running_total = sum(amount) over(order by invoice asc)
, target_amount = @target_amount
from dbo.invoices
), net_amount as (
select *
, net_amount = target_amount - running_total
from input
), selection as (
select *
, new_amount = case when net_amount > 0 then amount else amount   net_amount end
, invoice_filter = case when lag(net_amount) over( order by invoice) < 0 then 0 else 1 end
from net_amount
) -- end result
select invoice
, original_amount = amount
, new_amount
from selection
where invoice_filter = 1

And the result looks like this:

 --------- ----------------- ------------ 
| invoice | original_amount | new_amount |
 --------- ----------------- ------------ 
| ABC     |          100.00 |     100.00 |
| DEF     |          125.00 |     125.00 |
| GHI     |          150.00 |      10.00 |
 --------- ----------------- ------------