StackinAndSlackin StackinAndSlackin - 25 days ago 7
SQL Question

SQL: Trying to flag the earliest shipment date within a the same customer ID and order number across all rows of a database

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

Answer

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 "Yes".

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 Yes.

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.

Comments