Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Matching approximate dates between two queries

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
TCristiano
Asked:
TCristiano
2 Solutions
 
GrahamSkanCommented:
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
 
SharathData EngineerCommented:
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
 
TCristianoAuthor Commented:
Thanks for the quick responses!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now