## Splitting allocations across multiple records in a single statement

January 17, 2013 Leave a comment

Today I was asked a question about how to appropriately split a quantity across multiple records, ordered by date. I thought about this for a moment, and then rattled off a few ways that the general problem could be handled. This first 2 were to loop through the data, and use a CTE, respectively. Then the question arose as to whether it could be done in a single statement. After a moment of pause, I channeled my inner Barney Stinson and proclaimed “Challenge Accepted!”.

As an example of working through this, lets say this was a payment that needed to be allocated across multiple invoices. We can set up this data as follows (run this in conjunction with each of the scripts below to set up the data):

declare @invoice Table (invoice_id int, invoiced_amount money, invoice_date date) insert into @invoice(invoice_id, invoiced_amount, invoice_date) values (1, 25, '2012-1-1'), (2, 50, '2012-1-2'), (3, 200, '2012-1-2'), (4, 100, '2012-1-3'), (5, 500, '2012-1-4') declare @payment money = 225

We now have 5 invoices across a few days, and a payment of 225 that we need to allocate across them. Let’s say that our method of applying this payment is by date and order created.

**Example 1: Using a While Loop**

This is the most common method I have encountered to resolve this problem. We simply loop through the data and keep track of the payment remaining in a variable, while applying the payment information in a table to keep track of what has been done. When there is no longer any payment remaining to apply, we are done.

declare @accounts_recievable table(invoice_id int, invoice_date date, invoiced_amount money, paid_amount money, remaining_amount as (invoiced_amount - paid_amount)) insert into @accounts_recievable(invoice_id, invoice_date, invoiced_amount, paid_amount) select invoice_id, invoice_date, invoiced_amount, 0 as paid_amount from @invoice declare @invoice_id int = 1 declare @working_payment money = @payment declare @working_payment_remaining money = 0 while @working_payment > 0 begin --find the total amount of the current row select @working_payment_remaining = @working_payment - invoiced_amount from @accounts_recievable t where t.invoice_id = @invoice_id --update the paid amount for the row update t set t.paid_amount = case when @working_payment_remaining > 0 then t.invoiced_amount else @working_payment end from @accounts_recievable t where t.invoice_id = @invoice_id set @working_payment = @working_payment_remaining set @invoice_id = @invoice_id + 1 end select * from @accounts_recievable

Output:

invoice_id | invoice_date | invoiced_amount | paid_amount | remaining_amount |

1 | 1/1/2012 | 25 | 25 | 0 |

2 | 1/2/2012 | 50 | 50 | 0 |

3 | 1/2/2012 | 200 | 150 | 50 |

4 | 1/3/2012 | 100 | 0 | 100 |

5 | 1/4/2012 | 500 | 0 | 500 |

**Example 2: Using a CTE**

With a CTE, we are essentially looping through the data as well. This example assumed an integer invoice_id that is used for ordering, but you could also modify to use a row number to get an ordered data set. The first select in the cte determines the results for the first row. The second “select” uses the results from the current row in conjunction with the results from the previous row to determine the running totals.

;with cte(invoice_id, invoice_date, invoiced_amount, paid_amount, payment_remaining) as ( select invoice_id, invoice_date, invoiced_amount, case when @payment - invoiced_amount > 0 then invoiced_amount else invoiced_amount - @payment end as paid_amount, case when @payment - invoiced_amount > 0 then @payment - invoiced_amount else 0 end as payment_remaining from @invoice i where invoice_id = 1 union all select i.invoice_id, i.invoice_date, i.invoiced_amount, case when t.payment_remaining - i.invoiced_amount > 0 then i.invoiced_amount else t.payment_remaining end as paid_amount, case when t.payment_remaining - i.invoiced_amount > 0 then t.payment_remaining - i.invoiced_amount else 0 end as payment_remaining from cte t inner join @invoice i on i.invoice_id = t.invoice_id + 1 ) select invoice_id, invoice_date, invoiced_amount, paid_amount, invoiced_amount - paid_amount as remaining_amount, payment_remaining from cte

Output:

invoice_id | invoice_date | invoiced_amount | paid_amount | remaining_amount | payment_remaining |

1 | 1/1/2012 | 25 | 25 | 0 | 200 |

2 | 1/2/2012 | 50 | 50 | 0 | 150 |

3 | 1/2/2012 | 200 | 150 | 50 | 0 |

4 | 1/3/2012 | 100 | 0 | 100 | 0 |

5 | 1/4/2012 | 500 | 0 | 500 | 0 |

**Example 3: Using SQL Server 2012 Windowing Functions**

SQL Server 2012 opens up a variety of new windowing functions. My favorite is being able to set the boundaries on your function such that you can control your running aggregate. While we can easily reference the data from the previous row in a CTE or a While Loop, this is more difficult when you are not looping over the data. However, now that we can set our window boundaries to shift the aggregate, it becomes easier. In the example below, the “Running total query” is to display the results of each running total. The keys here are the functions for limiting the boundaries.

Running total query:

select invoice_id, invoice_date, invoiced_amount, sum(invoiced_amount) over (order by invoice_date, invoice_id rows unbounded preceding) as total_applied, @payment - sum(invoiced_amount) over (order by invoice_date, invoice_id rows unbounded preceding) as payment_remaining, @payment - sum(invoiced_amount) over (order by invoice_date, invoice_id rows between unbounded preceding and 1 preceding) as prior_payment_remaining --shift one left to get the amount previous for the first negative. the previous amount is the remainder that would subtract from the next invoiced total from @invoice

Output:

invoice_id | invoice_date | invoiced_amount | total_applied | payment_remaining | prior_payment_remaining |

1 | 1/1/2012 | 25 | 25 | 200 | NULL |

2 | 1/2/2012 | 50 | 75 | 150 | 200 |

3 | 1/2/2012 | 200 | 275 | -50 | 150 |

4 | 1/3/2012 | 100 | 375 | -150 | -50 |

5 | 1/4/2012 | 500 | 875 | -650 | -150 |

The first running total (total_applied) uses “rows unbounded preceding”. This will sum everything from the first row (using the order specified) up to and including the current row.

The second running total (payment_remaining) is like the first, only changed to be the difference of the payment vs. the invoiced amount.

While the first 2 running totals use the default of “current row” as the right boundary, the third running total (prior_payment_remaining) gives us the running total through the previous row by performing the SUM from the first row (“unbounded preceding”) through the row prior to the current row (“1 preceding”). The importance here is that when applying payment to invoices, there is a specific row at which you can only apply a partial payment and you need to know what the amount is that you can apply. This happens to be the row where the payment_remaining turns negative. The true amount we want to subtract is the total that was remaining in the previous row, thus the shift in prior_payment_remaining.

Applying the payment using the running totals:

select invoice_id, invoice_date, invoiced_amount, case when payment_remaining > 0 then invoiced_amount when payment_remaining <= 0 and prior_payment_remaining > 0 then prior_payment_remaining else 0 end as paid_amount, case when payment_remaining > 0 then 0 --if the applied payment leaves you > 0, then the entire payment can be applied when payment_remaining <= 0 and prior_payment_remaining > 0 then invoiced_amount - prior_payment_remaining else invoiced_amount end as amount_remaining from ( select invoice_id, invoice_date, invoiced_amount, sum(invoiced_amount) over (order by invoice_date, invoice_id rows unbounded preceding) as total_applied, @payment - sum(invoiced_amount) over (order by invoice_date, invoice_id rows unbounded preceding) as payment_remaining, @payment - sum(invoiced_amount) over (order by invoice_date, invoice_id rows between unbounded preceding and 1 preceding) as prior_payment_remaining --shift one left to get the amount previous for the first negative. the previous amount is the remainder that would subtract from the next invoiced total from @invoice ) t

Output:

invoice_id | invoice_date | invoiced_amount | paid_amount | amount_remaining |

1 | 1/1/2012 | 25 | 25 | 0 |

2 | 1/2/2012 | 50 | 50 | 0 |

3 | 1/2/2012 | 200 | 150 | 50 |

4 | 1/3/2012 | 100 | 0 | 100 |

5 | 1/4/2012 | 500 | 0 | 500 |

There we have it! 3 ways to achieve the same results. The last of these methods satisfies allocating this payment across multiple invoices with a single statement. While many developers and DBAs are familiar with the concept of ROW_NUMBER(), few have taken the time to understand the variety of windowing functions and options that are truly available. In this case, the usage of “rows unbounded preceding” and “rows between unbounded preceding and 1 preceding” gives us the ability to manipulate our window for the aggregated payment allocations. This has functionality has proven very useful for me, and for those who have taken the time to understand the power that can be derived from applying it.