All geek questions in one place

Attach two tables to the same date or the nearest date (before or after)

I just got a lot of help from Gord Thompson on a similar issue ( Combine two tables by joining the same date or the nearest date (not just exact matches) ) but now I understand that my data is not what I expected. It turns out that I may have Lead_Dates later than Product_Interest_Dates, and this causes the previous SQL code to remove these cases. More specific:

I have two tables:

  • CustomerID
  • Lead_date
  • Lead_source

and

  • CustomerID
  • Product_Interest_Date
  • Product_Interest

I want the two to create a single table where for each CustomerID each Product_Interest is connected to a Lead_Source, which is the closest date ( before or after ). The resulting table will be:

CustomerID Product_Interest_Date Product_Interest Lead_Date (the closest entry in time to Product_Interest_Date) Lead_Source (the Lead_Source of the closest Lead_Date)

I have studied the Gord code but cannot bring this house. Following his example, graphically I want this: http://i.stack.imgur.com/4ZVDV.jpg

SQL for Sequence Stack Overflow NEW 1

 SELECT pi.CustomerID, pi.Product_Interest_Date, l.Lead_Date, Abs(pi.Product_Interest_Date-l.Lead_Date) AS Date_Gap FROM Test_PI pi INNER JOIN Test_Leads l

Stack Overflow NEW 2

 SELECT [Qaru NEW 1].CustomerID, [Qaru NEW 1].Product_Interest_Date, Min([Qaru NEW 1].Date_Gap) AS MinOfDate_Gap FROM [Qaru NEW 1] GROUP BY [Qaru NEW 1].CustomerID, [Qaru NEW 1].Product_Interest_Date;

The final

 SELECT Test_PI.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source FROM (Test_PI INNER JOIN ([Qaru NEW 2] INNER JOIN [Qaru NEW 1] ON ([Qaru NEW 2].CustomerID = [Qaru NEW 1].CustomerID) AND ([Qaru NEW 2].Product_Interest_Date = [Qaru NEW 1].Product_Interest_Date) AND ([Qaru NEW 2].MinOfDate_Gap = [Qaru NEW 1].Date_Gap)) ON (Test_PI.CustomerID = [Qaru NEW 2].CustomerID) AND (Test_PI.Product_Interest_Date = [Qaru NEW 2].Product_Interest_Date)) INNER JOIN Test_Leads ON ([Qaru NEW 1].CustomerID = Test_Leads.CustomerID) AND ([Qaru NEW 1].Lead_Date = Test_Leads.Lead_Date) GROUP BY Test_PI.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source;

I tried to combine all this into one code and could not get past the SQL FROM error! This is my specific question, how to write this in one SQL code?

 SELECT Test_PI.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source FROM (Test_PI INNER JOIN ( (SELECT latest.CustomerID, latest.Product_Interest_Date, Min(latest.Date_Gap) AS Min_Date_Gap FROM latest ) latest1 INNER JOIN (SELECT pi.CustomerID, pi.Product_Interest_Date, l.Lead_Date, Abs(pi.Product_Interest_Date - l.Lead_Date) AS Date_Gap FROM Test_PI pi INNER JOIN Test_Leads l ON pi.CustomerID = l.CustomerID ) latest ) ON Test_PI.CustomerID = latest1.CustomerID AND Test_PI.Product_Interest_Date = latest1.Product_Interest_Date INNER JOIN Test_Leads ON Test_Leads.CustomerID = latest1.CustomerID AND Test_Leads.Lead_Date = latest1.Lead_Date
+5
source share
1 answer

Now, when we consider both past and future values ​​[Lead_Date], I changed the data of the test data in a special case

Table: Test_PI

 CustomerID Product_Interest_Date Product_Interest ---------- --------------------- ---------------- 1 2014-09-07 Interest1 1 2014-09-08 Interest2 1 2014-09-15 Interest3 1 2014-09-28 Interest4

Table: Test_Leads

 CustomerID Lead_Date Lead_Source ---------- ---------- ----------- 1 2014-09-07 Source1 1 2014-09-14 Source2 2 2014-09-15 Source3 1 2014-09-16 Source4

We'll start by creating a saved Access request named [Date_Gaps]

 SELECT pi.CustomerID, pi.Product_Interest_Date, l.Lead_Date, Abs(DateDiff("d", pi.Product_Interest_Date, l.Lead_Date)) AS Date_Gap FROM Test_PI pi INNER JOIN Test_Leads l ON pi.CustomerID = l.CustomerID

return

 CustomerID Product_Interest_Date Lead_Date Date_Gap ---------- --------------------- ---------- -------- 1 2014-09-07 2014-09-07 0 1 2014-09-08 2014-09-07 1 1 2014-09-15 2014-09-07 8 1 2014-09-28 2014-09-07 21 1 2014-09-07 2014-09-14 7 1 2014-09-08 2014-09-14 6 1 2014-09-15 2014-09-14 1 1 2014-09-28 2014-09-14 14 1 2014-09-07 2014-09-16 9 1 2014-09-08 2014-09-16 8 1 2014-09-15 2014-09-16 1 1 2014-09-28 2014-09-16 12

Now request

 SELECT CustomerID, Product_Interest_Date, Min(Date_Gap) AS MinOfDate_Gap FROM Date_Gaps GROUP BY CustomerID, Product_Interest_Date

returns

 CustomerID Product_Interest_Date MinOfDate_Gap ---------- --------------------- ------------- 1 2014-09-07 0 1 2014-09-08 1 1 2014-09-15 1 1 2014-09-28 12

so if we just join the request for [Date_Gaps] to get [Lead_Date]

 SELECT mingap.CustomerID, mingap.Product_Interest_Date, Date_Gaps.Lead_Date FROM Date_Gaps INNER JOIN ( SELECT CustomerID, Product_Interest_Date, Min(Date_Gap) AS MinOfDate_Gap FROM Date_Gaps GROUP BY CustomerID, Product_Interest_Date ) mingap ON Date_Gaps.CustomerID = mingap.CustomerID AND Date_Gaps.Product_Interest_Date = mingap.Product_Interest_Date AND Date_Gaps.Date_Gap = mingap.MinOfDate_Gap

we get

 CustomerID Product_Interest_Date Lead_Date ---------- --------------------- ---------- 1 2014-09-07 2014-09-07 1 2014-09-08 2014-09-07 1 2014-09-15 2014-09-14 1 2014-09-15 2014-09-16 1 2014-09-28 2014-09-16

Please note that we receive two calls for 09-15, because they both have a gap of 1 day (before and after). Thus, we need to break this connection by wrapping the above request in the aggregation request using Min(Lead_Date) (or Max(Lead_Date) , your choice)

 SELECT CustomerID, Product_Interest_Date, Min(Lead_Date) AS MinOfLead_Date FROM ( SELECT mingap.CustomerID, mingap.Product_Interest_Date, Date_Gaps.Lead_Date FROM Date_Gaps INNER JOIN ( SELECT CustomerID, Product_Interest_Date, Min(Date_Gap) AS MinOfDate_Gap FROM Date_Gaps GROUP BY CustomerID, Product_Interest_Date ) mingap ON Date_Gaps.CustomerID = mingap.CustomerID AND Date_Gaps.Product_Interest_Date = mingap.Product_Interest_Date AND Date_Gaps.Date_Gap = mingap.MinOfDate_Gap ) GROUP BY CustomerID, Product_Interest_Date

to give us

 CustomerID Product_Interest_Date MinOfLead_Date ---------- --------------------- -------------- 1 2014-09-07 2014-09-07 1 2014-09-08 2014-09-07 1 2014-09-15 2014-09-14 1 2014-09-28 2014-09-16

So now we are ready to join the source tables

 SELECT Test_PI.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source FROM ( Test_PI INNER JOIN ( SELECT CustomerID, Product_Interest_Date, Min(Lead_Date) AS MinOfLead_Date FROM ( SELECT mingap.CustomerID, mingap.Product_Interest_Date, Date_Gaps.Lead_Date FROM Date_Gaps INNER JOIN ( SELECT CustomerID, Product_Interest_Date, Min(Date_Gap) AS MinOfDate_Gap FROM Date_Gaps GROUP BY CustomerID, Product_Interest_Date ) mingap ON Date_Gaps.CustomerID = mingap.CustomerID AND Date_Gaps.Product_Interest_Date = mingap.Product_Interest_Date AND Date_Gaps.Date_Gap = mingap.MinOfDate_Gap ) GROUP BY CustomerID, Product_Interest_Date ) closest ON Test_PI.CustomerID = closest.CustomerID AND Test_PI.Product_Interest_Date = closest.Product_Interest_Date ) INNER JOIN Test_Leads ON Test_Leads.CustomerID = closest.CustomerID AND Test_Leads.Lead_Date = closest.MinOfLead_Date

return

 CustomerID Product_Interest_Date Product_Interest Lead_Date Lead_Source ---------- --------------------- ---------------- ---------- ----------- 1 2014-09-07 Interest1 2014-09-07 Source1 1 2014-09-08 Interest2 2014-09-07 Source1 1 2014-09-15 Interest3 2014-09-14 Source2 1 2014-09-28 Interest4 2014-09-16 Source4
+6
source

Source: https://habr.com/ru/post/1204876/

More articles:


All Articles