Here's one:
/* This is a cursor that walks each customer and sets their balance
*/
/* based on thier last non-voided renewal (remember thats the only one that
*/
/* counted in 2.2 and lower). Also, the release scripts that update a
*/
/* database flips the balance, which is all about 99% of the users need.
*/
DECLARE @custid int, @invid int, @balance money, @amount money
DECLARE ub_cursor CURSOR
FOR Select CustomerID, Balance
From MasterAccounts
Order By CustomerID
OPEN ub_cursor
FETCH NEXT FROM ub_cursor INTO @custid, @balance
WHILE (@@fetch_status <> -1)
BEGIN
Select @invid = 0
Select @invid = (Select Max(i.InvoiceID)
From Invoices i, InvoiceItems ii
Where CustomerID = @CustID
and i.InvoiceID = ii.InvoiceID
AND i.Type <> 'VOID'
AND i.type not like '%invoice%'
AND ii.PaymentID is NULL)
IF @invid > 0
select @amount=amount From invoices Where InvoiceID = @invid
else
select @amount=0
Select @custid, @balance, @invid, @amount
Update MasterAccounts
Set Balance = @amount
Where Current of ub_Cursor
FETCH NEXT FROM ub_cursor INTO @custid, @balance
END
CLOSE ub_cursor
DEALLOCATE ub_cursor
For more information about this list, including removal,
please see http://www.iea-software.com/maillist.html