Splitting allocations across multiple records in a single statement

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.

Advertisements