CODEWITHSUNDEEP
mysql
Dror
Dror

Reputation: 1322

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.

Upvotes: 1

Views: 80

Answers (1)

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Related Questions