ADVICE on billing Query in SQL Server 2000

Question

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

0
4903.33
3457.69

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.

Thanks

Answer 1

Consider the following:

a payment either applies in full to a balance, applies in part to a balance, or overpays a balance.

Now, imagine that we could find, for any balance, the cumulative balance of invoices to date. Rather than imagine that, let's do it:

create view cumulative_balance as
select a.*, 
  (select sum( balance ) 
  from openitems b 
  where b.id = a.id and b.type = a.type and a.daysOpen >= a.daysOpen)
  as cumulative_balance
from openitems a;

Now we can find the first cumulative balance less than or equal to the payment, for any id and type, and store that, and daysOpen, and cumulative balance in server variables.

Then we update all openItems with that id and type, where daysOpen <= the value we got, setting all those balances to zero.

Then we find the first non-zero balance of that id and type, and set its balance to be it's balance - (payment - the cumulative balance we stored). if there's an overpayment, this balance will be correctly negative.

With the correct query, you'll be able to do the lookup and first update in one statement.

There are two problems. One is that you can't determine, of two or more alances with the same id and type and daysOpen, which should be paid first. Adding a unique id to your table would serve as a tie-breaker for those cases.

Second is the need to save the cumulative balance to use it in the query for the second update. if you designed your table correctly, with a column for invoice_amount that wasn't updated by payments, and a payment column that was, this would solve your problem.

An even better refactoring would be to have two tables, one for invoices and one for payment: then a view could just do all the work, by comparing cumulative balances to cumulative payments, producing a list of unpaid balances or overpayments.

In fact, I designed just such a system for a major mortgage guarantee company with the initials FM. It was a bit more complicated than what you have, in that balances were calculated from a number of formulas of amounts and percentages, and multiple payers (actually, insurers, this was for mortgages that had gone into default) had to be invoiced in a prescribed order according to other rules, per defauted mortgage.

All of this was done in views, with a short (100 line or so) stored procedure that essentially did what I've outlined above: used a view that ordered the billing of invoices by these rules, applied payments (in the view), calculating what additional payments to invoice on what date to which insurer. The stored procedure then just generated invoices for the current date (which current date could be set, again using a view, to any date for testing purposes).

(The irony is that I'd taken the job onteh promise I'd get to write C++; the only C++ I wrote used the Oracle and Sybase C APIs to transfer data from the Oracle system to the Sybase one.)

Answer 2

This should do it. I've declared a local variable, but you could make that a parameter from a stored procedure. I've also added an invoice_id to the table to uniquely identify invoices since id and date don't seem to be unique.

DECLARE
    @paid_amount DECIMAL(9, 2)

SET @paid_amount = 500

UPDATE
    OI
SET
    balance =
            CASE
                WHEN @paid_amount - SQ.running_total > balance THEN 0
                ELSE balance - (@paid_amount - SQ.running_total)
            END
FROM
    dbo.OpenItems OI
INNER JOIN (
    SELECT
        I1.id,
        I1.invoice_id,
        I1.date,
        ISNULL(SUM(I2.amount), 0) AS running_total
    FROM
        OpenItems I1
    LEFT OUTER JOIN OpenItems I2 ON
        I2.id = I1.id AND
        I2.type = 'INV' AND
        I2.daysopen > I1.daysopen
    GROUP BY
        I1.id,
        I1.invoice_id,
        I1.date
) AS SQ ON
    SQ.id = OI.id AND
    SQ.invoice_id = OI.invoice_id
WHERE
    @paid_amount > SQ.running_total

Answer 3

Unless this is a one time effort...

It sounds like this is buisness logic and belongs in your business layer of your application.

Answer 4

You would have to use a couple of cursors and two nested loops. (this may be a little slow)

One to read all the payments - I assume Customer, Amount

Then for each customer create another cursor for the open items.

The first loop will read payments until done

Within that loop open a new cursor for the customer's open items sorted by oldest first.

Loop through each open item and apply the payments as you described

Then get next payment.

Repeat until no more payments.