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