Sql/mysql: Split A Quantity Value Into Multiple Rows By Date

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 2

Solution 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:

WITHRECURSIVE cte AS (
    SELECT planning_start_date ASdate, 
           planning_end_date, 
           quantity / (DATEDIFF(planning_end_date, planning_start_date) +1) AS daily_qty
    FROM test
    UNIONALLSELECTdate+INTERVAL1DAY, planning_end_date, daily_qty
    FROM cte
    WHEREdate< planning_end_date
)
SELECT `date`, daily_qty
FROM cte
ORDERBY `date`

Demo on dbfiddle

Solution 2:

In MySLQ 8+, you can use a recursive CTE like this:

withrecursive cte(dte, planning_end_date, quantity, days) as (
      select planning_start_date as dte, planning_end_date, quantity, datediff(planning_end_date, planning_start_date) +1as days
      from t
      unionallselect dte +interval1dayas dte, planning_end_date, quantity, days
      from cte
      where dte < planning_end_date
     )
select dte, quantity / days
from cte;

Here is a db<>fiddle.

In earlier versions, you want a numbers table of some sort. For instance, if your table has enough rows, you can just use it:

select (planning_start_date +interval n.n day),
       quantity / (datediff(planning_end_date, planning_start_date) +1)
from t join
     (select (@rn :=@rn+1) as n
      from t crossjoin
           (select@rn :=0) params
     ) n
     on planning_start_date +interval n.n day<= planning_end_date;

You can use any table that is large enough for n.