Match dates (take closest date after another date) 1

[Drivium]

I googled high and low but could find anything to apply to this task. I have two tables - one table with a pull date field (date job started) and another date with an end date field (date job ended), and am trying to assign the correct end date to the correct pull date. The correct end date is the closest end date following the pull date. I created another field using ABS to find the difference of days between to take the minimum based on each pull date, but not quite sure how to manipulate it to achieve the results I need. Notice well LCP040 has 2 different jobs with 2 pull dates (requiring matching of 2 end dates).
1st table and field: ESP_Failures/PullDte.
2nd table and field: DIMSADMIN_DIMS_EVENT/END_DATE.
Example:

Well PullDte END_DATE DayDifference
LCP017 10/9/2009 10/12/2009 3
LCP017 10/9/2009 2/1/2011 480
LCP017 10/9/2009 5/6/2011 574
LCP040 3/30/2009 4/7/2009 8
LCP040 3/30/2009 2/11/2010 318
LCP040 3/30/2009 8/26/2010 514
LCP040 3/30/2009 5/13/2011 774
LCP040 8/17/2010 8/26/2010 9
LCP040 8/17/2010 5/13/2011 269
LYD035 3/5/2009 3/11/2009 6
LYD035 3/5/2009 12/2/2009 272
LYD035 3/5/2009 8/27/2010 540
LYD035 3/5/2009 1/6/2011 672

This is the result I'm aiming for:
Well PullDte END_DATE DayDifference
LCP017 10/9/2009 10/12/2009 3
LCP040 3/30/2009 4/7/2009 8
LCP040 8/17/2010 8/26/2010 9
LYD035 3/5/2009 3/11/2009 6

SQL used here:
SELECT ESP_Failures.Well, ESP_Failures.PullDte, DIMSADMIN_DIMS_EVENT.END_DATE, Min(Abs([DIMSADMIN_DIMS_EVENT].[END_DATE]-[PullDTE])) AS DayDifference
FROM ESP_Failures LEFT JOIN DIMSADMIN_DIMS_EVENT ON ESP_Failures.I_KEY = DIMSADMIN_DIMS_EVENT.I_KEY
GROUP BY ESP_Failures.Well, ESP_Failures.PullDte, DIMSADMIN_DIMS_EVENT.END_DATE
HAVING (((DIMSADMIN_DIMS_EVENT.END_DATE)>[PullDte]));

 

[PHV]

What about this ?

SELECT F.Well, F.PullDte, Min(E.END_DATE) AS EndDte, Min(E.END_DATE)-F.PullDte AS DayDifference
FROM ESP_Failures F
LEFT JOIN DIMSADMIN_DIMS_EVENT E ON F.I_KEY = E.I_KEY AND F.PullDte < E.END_DATE
GROUP BY F.Well, F.PullDte

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Drivium]

That's does the trick! Thank you!!