# 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