How do I get all dates between start date and end date for each task?

Question

In oracle database, I have a table stored TASK_ID, TASK_START_DATE, TASK_END_DATE. How do I create a table with each row showing TASK_ID and DATE? (DATE being each day from start til end)

TASK_ID	TASK_START_DATE	TASK_END_DATE
001	03-04-2021	05-04-2021
002	05-04-2021	07-04-2021

To

TASK_ID	DATE
001	03-04-2021
001	04-04-2021
001	05-04-2021
002	05-04-2021
002	06-04-2021
002	07-04-2021

Answer 1

Date and time functions are notoriously database dependent. However, in standard SQL, you can approach this using a recursive CTE:

with recursive dates as (
      select TASK_ID, TASK_START_DATE, TASK_END_DATE
      from t
      union all
      select TASK_ID, TASK_START_DATE + INTERVAL '1' DAY, TASK_END_DATE
      from dates
      where TASK_START_DATE < TASK_END_DATE
     )
select task_id, task_start_date
from cte;

Note that this suggests and approach. The specific syntax depends on the database you are using.