How to retrieve Closest date from another table dynamically MySQL

user_9386153 0
Viewed 1009

I have two tables:

INFO table

ID        LockDate            Investor 
157       10/15/2018          TEST1
VF1       09/02/2018          TEST2
LO2       05/01/2018          TEST3
09K       03/03/2012          TEST4
098       05/01/2012          TEST5
099       09/09/2012          TEST6
2YT       08/25/2012          TEST7

NUMBERS table

Dates              Amount
10/10/2018         25.10
08/31/2018         200.15
05/10/2018         15.251
03/03/2012         10.10
05/10/2012         15.251
08/31/2012         548.0

I want the query to select all the values in the INFO table and find the closest or equal date in the Numbers table and give me the Amount. So my results would be:

ID        LockDate            Investor         Amount
157       10/15/2018          TEST1            25.10
VF1       09/02/2018          TEST2            200.15
LO2       05/01/2018          TEST3            15.251
09K       03/03/2012          TEST4            10.10
098       05/01/2012          TEST5            15.251
099       09/09/2012          TEST6            548.0
2YT       08/25/2012          TEST7            548.0

By closest I mean equal or closest one whether it is lesser or greater than lock date.

This is the query I'm using but it is just retrieving the greater or equal one which doesn't work at all for me since I have to do it dynamically...

SELECT I.* ,
       N.Amount  FROM 
(
SELECT  A.*, MIN(NUM.Dates) AS XDATE
FROM INFO A
INNER JOIN NUMBERS AS NUM
ON NUM.Dates >= A.LockDate
GROUP BY A.ID
)AS RES
INNER JOIN NUMBERS AS N
ON N.Dates = I.XDATE

I will appreciate any help.

date mysql sql
2 Answers

You just need to find the absolute minimum value for LockDate minus Dates. This will give you the closest date; lesser or greater. Rest is easy.

SELECT info.*, numbers.*
FROM info
INNER JOIN (
    SELECT ID, MIN(DATEDIFF(GREATEST(LockDate, Dates), LEAST(LockDate, Dates))) Delta
    FROM info
    CROSS JOIN numbers
    GROUP BY ID
) g ON info.ID = g.ID
INNER JOIN numbers ON DATEDIFF(GREATEST(LockDate, Dates), LEAST(LockDate, Dates)) = g.Delta

SQL Fiddle

user_870150

Not sure what is your definition of "closest".

Here is my approach to get dates<=LockDate

http://sqlfiddle.com/#!9/8eea46/8

SELECT i.*, n.amount 
FROM info i
LEFT JOIN numbers n
ON i.LockDate >= n.dates
LEFT JOIN numbers n_all
ON i.LockDate >= n_all.dates
   AND n_all.dates > n.dates
WHERE n_all.dates IS NULL

Note: expected result is different since the definition of "closest" kind changed.

PS

Q: Why do I think that is a good approach?

A: Because whenever we deal with the data related to the timeline usually we expect data to know what (state, events, results) was before the moment we have on the timeline but not what will happen in future. That mean 31/12/2017 line/record can have/collect data from any table/records before 31/12/2017 but none from 2018. This strategy helps to keep reports consistent. They less dependent on the date when we generate the report. if we generate report about Dec 2017 at 1st Jan 2018 it will output same result as if we generate same report on same period Dec 2017 but week or month later 10th Jan 2018 or 1st of Feb.

user_44214740
Related