Checking for renewals

[sudhirhinduja]

Hello,

The general layout is, premCust contains a customer's information, which is tied to premInvoice, which lists all the invoices, which ties to premInvoiceLines, which is all the items in the invoice that the customer ordered.

In premInvoiceLines is a field called ExpDate, which indicates the date on which the customer's particular subscription will expire.

Also tied to premCust is premRenew. This table is manually updated whenever an existing customer renews their subscription (which requires a new invoice, obviously).

I need your help designing a query. I want to find everyone whose account has expired but they have NOT renewed. So it would be something like:

Find CustID where (MOST RECENT RenewalDate < MOST RECENT ExpDate) OR (MOST RECENT ExpDate < Now() AND IsNull(RenewalDate))

I have made two queries to get back MaxOfExpDate and MaxOfRenewalDate which solves the first part.

For the second part where I check for RenewalDate being Null, I have only 3 fields in the premRenew table i.e. RenewID,RenewDate,CustID.

So what I want to achieve is:
Identify clients who have NEVER renewed AND their account have expired. This is what the null part of the query is for - clients who don't have anything in the renew table.

Any help is appreciated.

Thanks,