Query Assistance (Payments to Invoices)

Last Post 13 Aug 2009 11:16 AM by cyorka. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

09 Apr 2009 11:55 AM
I need some advice in tackling a query. I can handle this in a front-end application, however, due to design, I have to inplement this in the back-end. I have the following

CREATE TABLE [dbo].[openitems]( [id] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [type] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [date] [smalldatetime] NULL, [amount] [decimal](9, 2) NULL, [daysOpen] [smallint] NULL, [balance] [decimal](9, 2) NULL) ON [PRIMARY]

insert into openitems values('A12399','INV','2008-12-05',491.96,123)
insert into openitems values('A12399','INV','2008-12-12',4911.37,116)
insert into openitems values('A12399','INV','2008-12-05',3457.69,109)

The table above have all open invoices for a customer. I need to apply a payment to these invoices starting from the oldest invoice (daysOpen column in the table). So if I have a $550.00 payment, I'll first apply it to the invoice with 123 daysOld, that's $491.96 -$500 (which leaves $8.04 to be applied to the next invoice... and so on), then update that record (balance column in table) to 0.00 and move to the next and apply the remaining. That would be $4911.37 - $8.04, which would leave $4903.33. Since there is no balance left to be applied, the loop exits.

The balance column should now read


Note: I need to do this for all customers in a table (around 10,000). A customer has an average of about 20 invoices open.

New Member
New Member

13 Aug 2009 11:16 AM
I'm guessing you found a solution to this since the original post was in April. However, I thought I'd take a shot since nobody has replied yet. This solution does not use looping and also assumes the balance starts out (when row is first created) the same as the amount (which I renamed to be orig_amount). This assumption may or may not work for you.

The query works by using the total balance of all older [daysopen] rows (derived table named 'prior') and subtracting from @payment and current balance. If you break down the case statements you'll quickly see how the calculations work. The main query can be easily integrated into an UPDATE statement. Be sure to fully test this.

Some things to keep in mind (if you haven't already thought of them):
- how to handle invoices with same daysopen
- consider adding a unique identifier for easier linking

declare @maxdaysopen smallint,
@id char(8),
@payment decimal(9, 2)

set @id = 'A12399'

select @Payment = 1000.00,
@maxdaysopen = (select max(daysopen) from openitems where id = @id)

-- main query
select oi.id,

oi.balance 'old_balance',

case when oi.daysopen = @maxdaysopen -- oldest invoice
then case when @payment > oi.balance
then 0.00
else (oi.balance - @payment)
else case -- all other invoices
when (balance - (@payment - prior.prior_total)) < balance
then case
when (balance - (@payment - prior.prior_total)) < 0
then 0.00
else (balance - (@payment - prior.prior_total))
else balance
end 'new_balance'

from openitems oi
inner join (
select id,
select coalesce(sum(balance), 0.00) 'prior_total'
from openitems
where id = @id
and daysopen > oi.daysopen
) 'prior_total'
from openitems oi
) prior
on oi.id = prior.id
and oi.daysopen = prior.daysopen
where oi.id = @id
order by oi.daysopen desc
You are not authorized to post a reply.

Acceptable Use Policy