Join Tables on Nearest Date in the Past, in MySQL

Join tables on nearest date in the past, in MySQL

SELECT a.id, a.sales, a.date, (SELECT TOP 1 Goal 
                               FROM TableB b WHERE b.date < a.date
                               ORDER BY b.date DESC) As Goal
FROM TableA a

Going off the nearest date in the past.

MySQL LEFT JOIN ON Closest Date

Seems like you want to join on closest date that's smaller. Have you tried:

Select Range.date, A, B
from Range inner join Metric
on Metric.date = (select max(date) from Metric where date < Range.date);

MySQL join 1-to-1 by closest datetime

It seems the only way to achieve approximate 1-to-1 join is by using a cursor within the stored procedure.

Thank you @Strawberry for pointing me in the right direction - you will see pieces of your code reused below. Here is the solution that eventually worked for me. It outputs records sorted differently, but at least it is truly 1-to-1 match.

DROP PROCEDURE IF EXISTS amerge;

DELIMITER //

CREATE PROCEDURE amerge()

BEGIN
  /* Necessary declarations */
  DECLARE o_ID INT DEFAULT 0;
  DECLARE o_date VARCHAR(30) DEFAULT 0;
  DECLARE o_old VARCHAR(2);
  DECLARE o_mdiff FLOAT;
  DECLARE ct INT DEFAULT 0;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor1 CURSOR FOR SELECT ID, date, old, mdiff FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  /* Temporary tables */
  -- copy of 'old' with diff column = min difference
  CREATE TEMPORARY TABLE t1
    SELECT old.*,MIN(ABS(TIMESTAMPDIFF(hour, old.date, new.date))) AS mdiff
      FROM old JOIN new ON old.ID=new.ID
      GROUP BY old.ID, old.date
      ORDER BY mdiff ASC;

  -- cartesian join with abs(diff) column
    CREATE TEMPORARY TABLE t2
    SELECT old.ID AS ID_1, old.date AS date_1, new.ID as ID_2, new.date AS date_2, old, new,
        ABS(TIMESTAMPDIFF(hour, old.date, new.date)) AS diff
      FROM old CROSS JOIN new ON old.ID=new.ID
      ORDER BY diff ASC;

  -- empty table to fill in with the results
  CREATE TEMPORARY TABLE t3
    (id_1 INT, date_1 DATETIME, id_2 INT, date_2 DATETIME, old VARCHAR(2), new VARCHAR(2), diff FLOAT);

  /* Cursor */
  OPEN cursor1;
  getparams: LOOP
        FETCH cursor1 INTO o_ID, o_date, o_old, o_mdiff;
        IF done THEN
            LEAVE getparams;
        END IF;
      SELECT COUNT(*) FROM t2 WHERE t2.ID_1=o_ID AND t2.date_1=o_date AND t2.old=o_old AND t2.diff=o_mdiff INTO ct;
    CASE ct
        WHEN 0 THEN
          INSERT INTO t3 VALUES (o_ID, o_date, NULL, NULL, o_old, NULL, o_mdiff);
        ELSE
          INSERT INTO t3 SELECT * FROM t2 WHERE t2.ID_1=o_ID AND t2.date_1=o_date AND t2.old=o_old AND t2.diff=o_mdiff LIMIT 1;
    END CASE;
    DELETE FROM t2 WHERE t2.ID_2=o_ID AND t2.date_2 IN (SELECT date_2 FROM t3 WHERE t3.date_1=o_date);
    END LOOP getparams;
    CLOSE cursor1;

  /* Workaround for error of reopening temp tables in MySQL */
  DROP TEMPORARY TABLE t2;
  CREATE TEMPORARY TABLE t2
      SELECT * FROM t3;

  /* Output */
  SELECT * FROM t2
  UNION
  SELECT NULL AS ID_1, NULL AS date_1, new.ID as ID_2, new.date AS date_2, NULL AS old, new.new AS new, NULL AS diff
    FROM new LEFT JOIN t3 ON t3.ID_2=new.ID AND t3.date_2 = new.date WHERE t3.ID_2 IS NULL;

END //

DELIMITER ;

CALL amerge();

And the output is (using data from the above example, with PRIMARY key set to ID+date):

id_1             date_1 id_2             date_2  old  new diff  
1   2016-03-07 12:20:00 1   2016-03-07 12:20:00    a    u    0  
1   2016-04-02 12:20:00 1   2016-04-02 12:20:00    b    v    0  
1   2016-03-03 12:20:00 1   2016-03-02 12:20:00    e    t   24  
1   2016-03-01 10:09:00                    NULL    c NULL   26  
1   2015-04-12 10:09:00                    NULL    d NULL 7802  
                   NULL 2   2016-04-12 11:03:00 NULL    x NULL      

MySql select the nearest date in a jointure

Your subquery doesn't select the row with the minimum date difference, it just calculates the date difference. And nearest.id is every ID in the table.

We need to use TIME_TO_SEC(TIMEDIFF()) rather than DATEDIFF() because some of the bookings are on the same day.

The subquery should be grouped by customer ID, not booking ID, and needs to give an alias to the time difference so we can use it in the outer query. The join needs to match the time difference to the booking table.

SELECT c.name, b.number, b.start_date
FROM customer c LEFT JOIN booking b ON (c.id = b.customer_id)
INNER JOIN (
    SELECT customer_id, MIN(ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date)))) as mindiff
    FROM booking
    GROUP BY customer_id
) nearest ON b.customer_id = nearest.customer_id AND ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date))) = mindiff

SQLFIDDLE

To get all the customers, put the join between booking and the subquery that finds the nearest booking by customer into a subquery, then left join that with customer.

SELECT c.name, x.number, x.start_date
FROM customer c 
LEFT JOIN 
(SELECT b.customer_id, b.number, b.start_date
 FROM booking b
 INNER JOIN (
    SELECT customer_id, MIN(ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date)))) as mindiff
    FROM booking
    GROUP BY customer_id
  ) nearest ON b.customer_id = nearest.customer_id AND ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date))) = mindiff
) AS x ON c.id = x.customer_id

SQLFIDDLE

SQL Join on Nearest less than date

I believe this subquery will do it (not tested).

select *, 
   (select top 1 Discount 
    from table2 
    where table2.Date <= t.Date 
    order by table2.Date desc) as Discount
from Table1 t

Perhaps not the most performant however.

Edit:

Test code:

create table #table1 ([date] datetime, val int)
create table #table2 ([date] datetime, discount int)

insert into #table1 ([date], val) values ('1/26/2010', 10)
insert into #table1 ([date], val) values ('1/25/2010', 9)
insert into #table1 ([date], val) values ('1/24/2010', 8)
insert into #table1 ([date], val) values ('1/24/2010', 9)
insert into #table1 ([date], val) values ('1/23/2010', 7)
insert into #table1 ([date], val) values ('1/22/2010', 10)
insert into #table1 ([date], val) values ('1/21/2010', 11)

insert into #table2 ([date], discount) values ('1/26/2010', 2)
insert into #table2 ([date], discount) values ('1/23/2010', 1)
insert into #table2 ([date], discount) values ('1/20/2010', 0)

select *, 
   (select top 1 discount 
    from #table2 
    where #table2.[date] <= t.[date]
    order by #table2.[date] desc) as discount
from #table1 t

drop table #table1
drop table #table2

Results:

2010-01-26 00:00:00.000 10  2
2010-01-25 00:00:00.000 9   1
2010-01-24 00:00:00.000 8   1
2010-01-24 00:00:00.000 9   1
2010-01-23 00:00:00.000 7   1
2010-01-22 00:00:00.000 10  0
2010-01-21 00:00:00.000 11  0

How to retrieve Closest date from another table dynamically MySQL

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

---

Related Topics