I have a database containing prices from various suppliers for the same product. Each supplier/product combination has a start & end date with a product ID and price
id | pid | aid | start_date | end_date | price
1 | 1 | 1 | 2017-01-01 | 2017-01-10 | 10.00
1 | 1 | 2 | 2017-01-01 | 2017-01-05 | 12.00
1 | 1 | 2 | 2017-01-06 | 2017-01-09 | 9.00
I use a calendar table to make sure I have all the dates in a certain range. The struggle I have is to select the min price given a certain date range. The above data should output as below.
date | aid | price
2017-01-01 | 1 | 10.00
2017-01-02 | 1 | 10.00
2017-01-03 | 1 | 10.00
2017-01-04 | 1 | 10.00
2017-01-05 | 1 | 10.00
2017-01-06 | 2 | 9.00
2017-01-07 | 2 | 9.00
2017-01-08 | 2 | 9.00
2017-01-09 | 2 | 9.00
2017-01-10 | 1 | 10.00
Just having one supplier and getting the price is not an issue, but as soon as I start grouping the data I only get one result or an incorrect result. I'm using this query, which provides the wrong outcome.
SELECT
c.date, min(p.price) as min_price
FROM
bricks_calender c
LEFT JOIN
bricks_prijzen p ON c.date BETWEEN p.start_date AND p.end_date
WHERE
p.pid = 1
GROUP BY
aid
ORDER BY
c.date
Any suggestion where I need to update this query, to get the expected outcome? Or should I change my data model (which is of course not preferred)