How can I spread a value across multiple rows without a cursor?

Question

Is there an easy way to spread a value across multiple rows?

For example, my table contains

Type      Invoiced    Paid    Current
Charge    100         0       100
Charge    100         0       100
Charge    100         0       100
Payment   0         250       0
Payment   0          25       0

The data is imported this way, but I need to populate the Current and Paid columns with whatever they should be for that transaction based on the payment transactions that were also imported.

Is there an easy way to write a query to determine the balance for the Current column for each record?

For example, the 250 would apply 100 for the first two records and 50 to the next two, and the 25 would get applied to the last one, so the end result after updating the Current balance in my table should be:

Type      Invoiced    Paid    Current
Charge    100         100     0
Charge    100         100     0
Charge    100          75     25
Payment   0           250     0
Payment   0            25     0

I'd ideally like to do this with a single query instead of using a cursor to process each item individually. I've been trying to do it by using the Row_Number() function and joining two subqueries, but I know I'm missing something here

Here was my first attempt, which resulted in getting the running total of the current balance

;with cte(invoiced, paid, current)
as (
    select invoiced, paid, current
        , row_number() over (order by datecreated)
    from mytable
)

select t1.invoiced, t1.paid, sum(t2.invoiced - t2.paid) as [current]
from cte as t1
join cte as t2 on t1.number = t2.number and t2.rownum <= t1.rownum
group by t1.uid, t1.number, t1.rownum
order by t1.rownum

Result:

Invoiced    Paid    Current
100         0       100
100         0       200
100         0       300
0         250       50
0          25       25

I'm sure there's a way to do this, but right now my brain seems on strike and is refusing to come up with a solution.

Answer 1

I think I found a solution

First off, I don't need to link the Paid transactions to the Invoiced transactions, so I only need the sum of all payments

select accountid, sum(paid)
from mytable
where type = 'Payment'
group by accountid

Then I need to apply this value to each record until the running total becomes greater than the total paid.

To do this, I modified my running total query so it only sums the charges instead of summing both charges and payments

;with cte(id, accountid, invoiced, paid, current)
as (
    select id, accountid, invoiced, paid, current
        , row_number() over (order by datecreated)
    from mytable
    where type = 'Charge'
)

select t1.id, t1.accountid, t1.invoiced, sum(t2.invoiced) as [runningTotalOfCharges]
from cte as t1
join cte as t2 on t1.number = t2.number and t2.rownum <= t1.rownum
group by t1.id, t1.accountid, t1.invoiced

and joined that to the payment query, so now I have a bunch of rows containing the total payment amount, the running total of charges up until that record, and the current record's charge amount.

From there, I just needed a CASE statement to determine if the charge was fully paid, partially paid, or not paid at all, and use a little math to figure out the Paid and Current records

select charged.Id, charged.AccountId, charged.Invoiced
    -- Use Case statements to determine if this payment is fully paid, partially paid, 
    -- or not paid at all, then determine Current and Paid based on that
    , case when totalpaid - runningtotal >= 0 then invoiced 
        when invoiced > abs(totalpaid - runningtotal) then invoiced + totalpaid - runningtotal
        else 0 end as [Paid]
    , case when totalpaid - runningtotal >= 0 then 0 
        when invoiced > abs(totalpaid - runningtotal) then abs(totalpaid - runningtotal)
        else invoiced end as [Current]
from 
(
    -- Running total query from above
    select t1.id, t1.accountid, t1.invoiced, sum(t2.invoiced) as [runningtotal]
    from cte as t1
    join cte as t2 on t1.number = t2.number and t2.rownum <= t1.rownum
    group by t1.id, t1.accountid, t1.invoiced
) as charged

inner join (
    -- Total Paid query from above
    select accountid, sum(paid) as totalpaid
    from mytable
    where type = 'Payment'
    group by accountid
) as paid on charged.number = paid.number

And the end result is what I want. Just need to join this to the actual data table via the Id column, and update the Paid and Current values :)

Id    AccountId    Invoiced    Paid    Current
1     1            100         100     0
2     1            100         100     0
3     1            100         75      25