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
3
Medium Priority
?
415 Views
Last Modified: 2013-12-25
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
Comment
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
  • Learn & ask questions
3 Comments
 
LVL 76

Accepted Solution

by:
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

by:Sharath
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)

Open in new window

0
 

Author Closing Comment

by:TCristiano
ID: 35131188
Thanks for the quick responses!
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

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

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question