Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Matching approximate dates between two queries

Posted on 2011-03-11
Medium Priority
415 Views
Hello-

Is there sql that will allow for an approximate match on dates between two tables with a one-to-many relationship?

Essentially, this mimics the Microsoft Excel vlookup funtion using the 'True' designation of finding an approximate match.  I've attached a simple example.

Thanks!
Todd.
EExchange.pdf
0
Question by:TCristiano
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 76

Accepted Solution

GrahamSkan earned 252 total points
ID: 35113517
Not sure how approximate you need it. This requires the two dates to be within 100 days of each other. It produces the result that you show in the PDF.

SELECT Table1.[Asset Name], Table1.[Acquisition Date], Table1.Cost, Table2.Year, Table2.Index
FROM Table1, Table2
WHERE (Abs([Acquisition Date]-[Table2].[Year])<100);
0

LVL 41

Assisted Solution

Sharath earned 248 total points
ID: 35113622
Can you check this?
``````SELECT *
FROM Table1 AS t1,
Table2 AS t2
WHERE ABS(DATEDIFF(d,t1.Acquisition_Date,t2.Year)) = (SELECT MIN(ABS(DATEDIFF(d,t1.Acquisition_Date,t2.Year)))
FROM Table1 AS t3,
Table2 AS t4
WHERE t1.AssetName = t3.t1.AssetName
AND t1.Acquisition_Date = t3.Acquisition_Date
AND t1.Cost = t3.Cost)
``````
0

Author Closing Comment

ID: 35131188
Thanks for the quick responses!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
###### Suggested Courses
Course of the Month8 days, 10 hours left to enroll