Dror Dror - 4 months ago 7
MySQL Question

Receive range of dates. If starting dating doesn't exist fetch range from the closest past date

I am looking for a MySQL query that gets me historical price changes of a product to display later on a chart - based on range of dates.

For example:

Select * from HistoryPrices
where StartDate>='1-1-2016' and EndDate<='1-20-2016'
where ProductID=505


This is fine. This is where it becomes more interesting.
If
1-1-2016
doesn't contain any product price change, I will need to fetch the past closest date of
1-1-2016
and fill it out for
1-1-2016
in the results.

Lets say
12-25-2015
is the closest past date of price change.

Example:

table looks like:

Date Price
12-25-2015 44.5
1-3-2016 50.5
1-4-2016 45.6
1-10-2016 40.99
1-15-2016 50.50
1-22-2016 50.99


MySQL query result should look like (from 1-1-2016 to 1-20-2016):

1-1-2016 44.5
1-3-2016 50.5
1-4-2016 45.6
1-10-2016 40.99
1-15-2016 50.50


Notice price of
1-1-2016
got
12-15-2015
price.

Looking for an MySQL query that can achieve this result.

JPG JPG
Answer

Try this for your sample date:

select *
from HistoryPrices
where `Date` >= date('2016-01-01') and `Date` <= date('2016-01-20')
union (
    select '2016-01-01', Price
    from HistoryPrices
    where `Date` <= date('2016-01-01')
    order by `date` desc
    limit 1
)
order by `date`

Demo Here

And Demo with 2016-01-01 included in data