I’m bulding a Project Manager System using PHP and MySQL, among other languages, and I’m strugling to query my data in order to create a S-Curve Chart. In my data base I have the following table named “Tasks”

To draw the chart, I need to have a row for every day between the minimum date and the maximum date of the Task Table table, even if this exact date does not exist in the task table (example: 2021-12-04 – it is not written on the table, but it happens between 2021-12-01 and 2021-12-07). And, for every day, I need to count the duration of all the tasks in days that exist before the date and the same for only the completed tasks (I’ll try to explain better with an example):

Explanation: The last day (2020-12-07) is the last date that appear on the Task Table. For that day, I’d have do count 17 “Days Task” (6 days for the Task 1 becouse all the 6 days of duration happend before 2020-12-07 + all the days for Task2 + all the day for the duration of task 3) The same goes for the completed “Days Task” but only counting day of completed tasks – for that reason, not considering Task 1 since it is “Pendent”).

I honestly, have no idea how to perform that using MySQL. I tryed a bunch of queries and nothing seemed to work as I wanted.

I was able to get a Table with all the dates using:

@i<-1;
SELECT DATE(ADDDATE((SELECT MIN(Beginning_Date) from tasks), INTERVAL @i:=@i+1 DAY)) AS date FROM `tasks`
HAVING 
@i < DATEDIFF((SELECT MAX(End_date) from tasks), (SELECT MIN(Beginning_Date) from tasks )))

However, I have no idea how to count the tasks for these dates.

Does anyone knows how to do it?