Match closest date from a table to a table with just dates

All we need is an easy explanation of the problem, so here it is.

I am trying to feel some gaps in table with values from a previous date.
I have created a date table which looks like this:

date
2021-04-13
2021-04-14
2021-04-15
2021-04-16
2021-04-17
2021-04-18
2021-04-19
2021-04-20

And main table which looks like this:

capture_date company value
2021-04-13 Electronic Arts Inc. 3.01
2021-04-14 Electronic Arts Inc. 2.99
2021-04-16 Electronic Arts Inc. 3.03
2021-04-17 Electronic Arts Inc. 3.05
2021-04-20 Electronic Arts Inc. 3.04

And the main result i want to achieve is this:

date capture_date company value
2021-04-13 2021-04-13 Electronic Arts Inc. 3.01
2021-04-14 2021-04-14 Electronic Arts Inc. 2.99
2021-04-15 2021-04-14 Electronic Arts Inc. 2.99
2021-04-16 2021-04-16 Electronic Arts Inc. 3.03
2021-04-17 2021-04-17 Electronic Arts Inc. 3.05
2021-04-18 2021-04-17 Electronic Arts Inc. 3.05
2021-04-19 2021-04-17 Electronic Arts Inc. 3.05
2021-04-20 2021-04-20 Electronic Arts Inc. 3.04

I have made a query that does this:

SELECT c.date, e.capture_date, company, value FROM `calendar` c
INNER JOIN companies e ON
e.capture_date = (SELECT e1.capture_date
                  FROM companies e1
                  WHERE e1.capture_date <= c.date
                  ORDER BY capture_date DESC
                  LIMIT 1)
WHERE e.company='Electronic Arts Inc.';

However for a company like:

capture_date company value
2021-04-13 Apple 3.01

The same query is giving me this result:

date capture_date company value
2021-04-13 2021-04-13 Apple 3.01

When I need this:

date capture_date company value
2021-04-13 2021-04-13 Apple 3.01
2021-04-14 2021-04-13 Apple 3.01
2021-04-15 2021-04-13 Apple 3.01
2021-04-16 2021-04-13 Apple 3.01
2021-04-17 2021-04-13 Apple 3.01
2021-04-18 2021-04-13 Apple 3.01
2021-04-19 2021-04-13 Apple 3.01
2021-04-20 2021-04-13 Apple 3.01

Is there a solution to this problem?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You need:

  1. Take a date from the calendar
  2. Take a row from the table which is not above the date from the calendar
  3. Ensure that there is no another row in the table with the date between the dates from steps 1 and 2 (which’ date is above the date taken on the step 2 but not above than the date taken on the step 1)

So this will be

SELECT *
FROM calendar t1
JOIN datatable t2 ON t1.date >= t2.date
WHERE NOT EXISTS ( SELECT NULL
                   FROM datatable t3
                   WHERE t1.date >= t3.date
                     AND t3.date > t2.date )

UPDATE

The code was updated. The names of tables/columns used are taken from the question. The condition which allows to select one company from a lot of companies in the table added.

Query text:

SELECT *
FROM calendar t1
JOIN companies t2 ON t1.`date` >= t2.capture_date
WHERE NOT EXISTS ( SELECT NULL
                   FROM companies t3
                   WHERE t1.`date` >= t3.capture_date
                     AND t3.capture_date > t2.capture_date
                     AND t2.company = t3.company )
  AND t2.company = @company_name;

DEMO fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply