I run this sql script after every batch renewal. It will highlight any
users that have Invoices with an end date greater than the expiry date.
Try it out and see if it shows those 10 customers that cannot be renewed.
------------------------------
SELECT ma.LastName, ma.FirstName
FROM MasterAccounts ma
WHERE ma.Active=1 AND
EXISTS (SELECT InvoiceID
FROM Invoices
WHERE Invoices.CustomerID=ma.CustomerID AND
Invoices.EndDate > ma.maExpireDate AND
Invoices.Type <> 'Void' AND
Invoices.Type <> 'Renewal' AND
NOT EXISTS (SELECT InvoiceID
FROM Invoices
WHERE Invoices.CustomerID=ma.CustomerID AND
Invoices.EndDate > ma.maExpireDate AND
Invoices.Type = 'Renewal' ))
-------------------------------
If you DO find a few customers that have this problem, the way I fix it
is to find out the ID of the invoice that is causing the problem and then
set the end date on that invoice to the same as the Expiry date. The
following script should do the trick.
-------------------------------
DECLARE @InvID integer
SELECT @InvID = {Type in number of the Invoice you want to change}
UPDATE invoices
SET enddate = (SELECT maexpiredate FROM masteraccounts, invoices
WHERE masteraccounts.customerid = invoices.customerid AND
invoices.invoiceid = @InvID)
WHERE invoiceid = @InvID
-------------------------------
Standard disclaimer applies to the above script: if it bungles your
system, don't blame me. I am not responsible, and I have never been
responsible ... you just have to ask my family for proof of that *grin*.
--------------------------------------#------------------------------------
David Khoury | "Where's the KABOOM ??
Technical Manager & Comms Engineer | There was supposed to be an
dave@flex.com.au | earth shattering KABOOM!!"
http://www.flex.com.au/~dave | Marvin the Martian
--------------------------------------#------------------------------------