SQL/MySQL: split a quantity value into multiple rows by date

Question

I have a table with three columns: planning_start_date - planning_end_date - quantity.

For example I have this data:

 planning_start_date | planning_end_date | quantity
          2019-03-01 |        2019-03-31 |     1500

I need to split the value 1500 into multiple rows with the adverage per day, so 1500 / 31 days = 48,38 per day.

The expected result should be:

date         daily_qty
2019-03-01 |     48,38
2019-03-02 |     48,38
2019-03-03 |     48,38
...
2019-03-31 |     48,38

Anyone with some suggestions?

Answer 1

Should you decide to upgrade to MySQL 8.0, here's a recursive CTE that will generate a list of all the days between planning_start_date and planning_end_date along with the required daily quantity:

WITH RECURSIVE cte AS (
    SELECT planning_start_date AS date, 
           planning_end_date, 
           quantity / (DATEDIFF(planning_end_date, planning_start_date) + 1) AS daily_qty
    FROM test
    UNION ALL
    SELECT date + INTERVAL 1 DAY, planning_end_date, daily_qty
    FROM cte
    WHERE date < planning_end_date
)
SELECT `date`, daily_qty
FROM cte
ORDER BY `date`

Demo on dbfiddle