ADVICE on billing Query in SQL Server 2000

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

From stackoverflow

• 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.

  Miyagi Coder : I do not suggest using cursors for performace reasons...if you find your self performing business logic in SQL it should go in your business layer of your application...

  Miyagi Coder : unless this is a one time thing...

  DJ : I know - I don't recommend it either - but the OP wants to do it within the DB

  Jim G. : -1: Using cursors for this solution would impose a significant performance penalty.

• Unless this is a one time effort...

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

  Saif Khan : I know. Somehow I have to run this process nightly, which is not part of the application.

• 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.)

  Saif Khan : Sometimes it drives me nuts trying to implement Business Logic in the data store.

  tpdi : SQL is about sets; if you think it terms of sets it becomes obvious. Just as, say, Haskell seems weird and convoluted to programmers with a background only in imperative languages, but cleaner and sweeter to Haskell programmers.

• 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
  

  Saif Khan : This didn't work. The @paid_amount - SQ.running_total > balance can't work. The calculation must happen, and the difference must then pass to the next row. I believe a CURSOR might be the best solution. I was trying to avoid that.

  Tom H. : Did you actually test it? I just ran it again on a local database and it gave the exact results that you were looking for. Are you sure that you understand how it works? My guess is not, since you said that it "can't work". If not, I can explain it. You need to think in sets when working with SQL, not procedural code.