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.