>Good start, thanks Borislav
>
>>>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
>>
>>
>>How about:
>>
>>UPDATE TableA SET Expired = CASE WHEN ExpiryDate < TblB.ExpiryDate
>> THEN 'Yes'
>> ELSE 'No' END
>>FROM TableA
>>INNER JOIN (SELECT cCustomerID, cProductID, MAX(ExpiryDate) AS ExpiryDate
>> FROM TableA
>> GROUP BY cCustomerID, cProductID) TblB
>> ON TableA.cCustomerID = TblB.cCustomerID AND
>> TableA.cProductID = TblB.cProductID
>>
>>
>>NOT TESTED!!!
You could try also this:
UPDATE TableA SET Expired = 'Yes'
FROM TableA
INNER JOIN (SELECT cCustomerID, cProductID, MAX(ExpiryDate) AS ExpiryDate
FROM TableA
GROUP BY cCustomerID, cProductID) TblB
ON TableA.cCustomerID = TblB.cCustomerID AND
TableA.cProductID = TblB.cProductID AND
TableA.Expired < TblB.ExpiryDate
But be sure you have a real good backup first :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.