Create a view which lists all dates for a task

Question

For sql server I am trying to get the view as in picture below in green from the brown table.

What is want is a 'record' for each used date for a task.

Can this be done in SQL? Thanks for guidelines if possible.

Answer 1

Hi @Wannes Wannes

Please check this query:

;WITH CTE1 AS
(
 SELECT * FROM #temp
 UNPIVOT([Date] FOR [DateType] IN ([StartDate],[EndDate]))U
),CTE2 AS
(
 SELECT Task,[Date]
 FROM CTE1
 WHERE [DateType]='StartDate'
 UNION ALL
 SELECT C2.Task,DATEADD(DAY,1,C2.Date)
 FROM CTE2 C2 JOIN CTE1 C1 ON C2.Task=C1.Task AND C1.DateType = 'EndDate' AND C2.Date<C1.Date
)
SELECT * FROM CTE2
ORDER BY Task,[Date]

Output:

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you are on SQL 2022, Azure SQL Database or Azure Managed Instance, you can do this:

CREATE TABLE #temp (Task varchar(20) NOT NULL PRIMARY KEY,
                    StartDate date NOT NULL,
                    EndDate   date NOT NULL)

INSERT #temp(Task, StartDate, EndDate)
   VALUES('Task1', '20230312', '20230318'),
         ('Task2', '20230314', '20230318')

SELECT t.Task, Date = dateadd(DAY, gs.value, t.StartDate)
FROM   #temp t
CROSS  APPLY generate_series(0, datediff(DAY, t.StartDate, t.EndDate)) AS gs
ORDER BY t.Task, Date
go
DROP TABLE #temp

For older versions fo SQL Server - which does not have generate_series - you can use a table of numbers or a table of dates. See this short article on my web site for example and ideas: https://www.sommarskog.se/Short%20Stories/table-of-numbers.html.

Answer 3

Or with a recursive CTE = Common Table Expression: