2

I am trying to perform a join from table 1. In table 2 there could be multiple matches on the join, but I only want to use each match once and only once. How do I do this? Here is my data set:

Products

Product_Plan_ID      Product_Plan_Creation_Date
===============      =========================
123-3241             1/2/2018
123-3242             1/15/2018
123-3242             1/17/2018
123-3243             1/18/2018
123-3244             2/2/2018
123-3245             2/5/2018

AND...

Product_Delivery

Product_Plan_ID      Product_Plan_Delivery_Date
===============      =====================
123-3242             1/20/2018
123-3242             1/22/2018

Here is code to generate tables and data set...

CREATE TABLE Products
(
Product_Plan_ID varchar(25),
Product_Plan_Creation_Date date
)

create table Product_Delivery
(
Product_Plan_ID varchar(25),
Product_Plan_Delivery_Date date
)

INSERT INTO Products 
select '123-3241', '1/2/2018'
union all
select '123-3242', '1/15/2018'
union all
select '123-3242', '1/17/2018'
union all
select '123-3243', '1/18/2018'
union all
select '123-3244', '2/2/2018'
union all
select '123-3245', '2/5/2018'

INSERT INTO Product_Delivery
SELECT '123-3242', '1/20/2018'
UNION ALL
SELECT '123-3242', '1/22/2018'

In my query, I want to join the Products table to Product_Delivery and for each row in Products, I want to join it to Product_Delivery matching on the Product_Plan_ID and where the Product_Plan_Delivery_Date is within 30 days of the Product_Plan_Creation_Date. The kicker is, if I find a match in Product_Delivery, I only want that match to be used a single time. The way I have it now, I'm ending up with duplicates on Product_Plan_Delivery_Date 1/20/2018 below. Here's my query so far:

select Products.Product_Plan_ID, Products.Product_Plan_Creation_Date, 
t2.Product_Plan_Delivery_Date from Products
CROSS APPLY (
    SELECT TOP 1 t.Product_Plan_ID, t.Product_Plan_Delivery_Date 
    FROM Product_Delivery t
    where t.Product_Plan_Delivery_Date between 
    Products.Product_Plan_Creation_Date and dateadd(dd, 30, Products.Product_Plan_Creation_Date)
and Products.Product_Plan_ID = t.Product_Plan_ID
) t2

The problem with the above is I end up with a duplicate match for Product_Plan_ID 123-3242 on Product_Plan_Delivery_Date, 1/20/2018. How would I write this so I have Product_Plan_ID 123-3242 match first on 1/20/2018, then 2nd on 1/22/2018?

My desired result set would be this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/22/2018

However...I'm getting this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/20/2018

Thanks in advance.

  • 2
    Why type dd instead of day? Also, be very careful about regional dates like m/d/y. Much better to use unambiguous formats like yyyymmdd. And finally, BETWEEN is ok here, I recommend against it for date/range queries in general. See this and this. – Aaron Bertrand Feb 20 '18 at 14:43
  • Thanks for the feedback. Will keep in mind. I guess, I just always learned "dd" for day, so have always used that. – Joseph A Feb 20 '18 at 15:23
2

For future posts, it is better to provide CREATE TABLE statements and INSERT statements. It is also beneficial to add an expected result set. What I guess you are trying to do is to pair Product_Plan_Creation_Date and Product_Plan_Delivery_Date. You can create an ordering number with a WINDOW function:

SELECT Product_Plan_ID, Product_Plan_Creation_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Creation_Date) AS RN
FROM Products

and similar for Product_Delivery:

SELECT Product_Plan_ID, Product_Plan_Delivery_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
FROM Product_Delivery

Now you can use the ordering number to join these two derived tables:

SELECT A.Product_Plan_ID, A.Product_Plan_Creation_Date
     , B.Product_Plan_Delivery_Date
FROM (
    SELECT Product_Plan_ID, Product_Plan_Creation_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                              ORDER BY Product_Plan_Creation_Date) AS RN
    FROM Products
) AS A
JOIN (
    SELECT Product_Plan_ID, Product_Plan_Delivery_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
    FROM Product_Delivery
) AS B
    ON A.Product_Plan_ID = B.Product_Plan_ID
    AND A.RN = B.RN
  • 1
    This is a good start, but it's missing criteria to limit matches to 30 days. – Aaron Bertrand Feb 20 '18 at 14:39
  • ... which will probably require a recursive CTE (or maybe two cursors!) – ypercubeᵀᴹ Feb 20 '18 at 14:42
  • Yes, thank you for the prompt responses. I've revised my original query, to include the CREATE and INSERT statement as well as corrected the query to include the limit match on 30 days. Thank you everyone. – Joseph A Feb 20 '18 at 14:45
  • 1
    I see I missed the 30 day requirement. What should the effect be if say the 2:nd rows are more than 30 days apart? Should they both be skipped or should they be paired with another row that lies within 30 days? – Lennart Feb 20 '18 at 14:59
  • 1
    If the next row is not within 30 days, the one after that won't be either. :-) – Aaron Bertrand Feb 20 '18 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.