Hi all,
Customers buy a licence for a product every year. Details are logged in the table TableA below.

What I would like to achieve with an update sql is this:

I need help to craft an sql statement that will flag the ones that have expired. If a customer has never renewed the contract (ie there is only 1 record of cusomer/product in the table) that contract stays. If there is more than 1 record for customer and product then those with ExpiryDate less that today should be flagged expired. However if all the customer’s product licences have expired (however many) the most recent of the lot should be left as active.



TableA before update sql
nKey cCustomerID cProductID ExpiryDate Expired
1 Cust001 Prod001 01-01-2007 No
2 Cust001 Prod001 01-01-2008 No
3 Cust001 Prod001 01-01-2009 No
4 Cust001 Prod002 01-01-2008 No
5 Cust001 Prod002 01-01-2009 No
6 Cust002 Prod002 01-01-2009 No
7 Cust003 Prod003 01-01-2007 No
8 Cust003 Prod002 01-01-2009 No
9 Cust003 Prod003 01-01-2008 No
10 Cust004 Prod001 01-01-2007 No

TableA after update sql
nKey cCustomerID cProductID ExpiryDate Expired
1 Cust001 Prod001 01-01-2007 Yes
2 Cust001 Prod001 01-01-2008 Yes
3 Cust001 Prod001 01-01-2009 No
4 Cust001 Prod002 01-01-2008 Yes
5 Cust001 Prod002 01-01-2009 No
6 Cust002 Prod002 01-01-2009 No
7 Cust003 Prod003 01-01-2007 Yes
8 Cust003 Prod002 01-01-2009 No
9 Cust003 Prod003 01-01-2008 No
10 Cust004 Prod001 01-01-2007 No

Thanks in advance