Think of an order fulfillment database where each customer ID can have the same Order Number for a product shipment and its refills. I am trying to flag the refills by adding 'Y' to a new column for refills. The first shipment is identified by the earliest ship date in the database for the same customer ID and order number. The shipments after the first shipment date with the same customer ID and order number would be the refills.
Customer # and Order # are varchars. Date is a date type.
Table I currently have. I want to be able to fill a new column called "Refill" with Y or N:
Customer # Order # ShipDate Refill <---New Column I want to create
1234 2124 5/25/2015 Y
1234 2124 3/25/2015 N
1234 2124 4/25/2015 Y
5678 4439 12/25/2014 Y
5678 4439 2/20/2015 Y
5678 4439 9/10/2014 N
6666 5920 1/12/2012 Y
6666 5920 5/12/2011 N
6666 6053 6/12/2016 Y
6666 6053 4/12/2016 N
6666 6053 8/12/2016 Y
It appears that the logic for the update is that the initial shipping record for a given customer and order is
"No" but all subsequent records are
In the update query below I join your original table to a subquery which finds the initial shipping record for each customer/order group. Then, a record in your original table which does match must be a
No while a record which does not match must be a
UPDATE t1 SET Refill = CASE WHEN t2.Customer IS NULL THEN 'Yes' ELSE 'No' END FROM yourTable t1 LEFT JOIN ( SELECT Customer, Order, MIN(ShipDate) AS ShipDate -- this query finds FROM yourTable -- the original GROUP BY Customer, Order -- ship date ) t2 ON t1.Customer = t2.Customer AND t1.Order = t2.Order AND t1.ShipDate = t2.ShipDate WHERE t1.Order IS NOT NULL OR t1.ShipDate IS NOT NULL
This answer also assumes that you already have a
varchar column called
Refill defined. If you don't, then go ahead and create one.