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)
If bricks_calender contains one row per date, the follow should work:
SELECT
c.`date`,
(select s.`aid` from `bricks_prijzen` s where s.`price` = min(p.`price`) and c.`date` BETWEEN s.`start_date` AND s.`end_date` ORDER BY s.`price` ASC LIMIT 0,1) AS `aid`,
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
c.`date`
ORDER BY
c.`date`;
I think it's simply because you group your data by aid with GROUP BY aid, so you can't have more row than the number of different aid that exist.
By unsetting this GROUP BY, does it solve the problem?