How to generate all dates for a date range extracted from two date columns in SQL

Question

I have a table TEMP1 as :

Parameter   Val From         To
a           1   8/1/2018    8/5/2018
b           2   8/4/2018    8/8/2018
c           3   8/8/2018    8/13/2018

and i need the output like below:

Parameter   Date    Val
a   8/1/2018    1
a   8/2/2018    1
a   8/3/2018    1
a   8/4/2018    1
a   8/5/2018    1
b   8/4/2018    2
b   8/5/2018    2
b   8/6/2018    2
b   8/7/2018    2
b   8/8/2018    2
c   8/8/2018    3
c   8/9/2018    3
c   8/10/2018   3
c   8/11/2018   3
c   8/12/2018   3
c   8/13/2018   3

using SQL.

Answer 1

Use the select below to get the dates, join in your TEMP1 table to get the values you want.

CREATE TABLE #tmpDates(  StartDate DATETIME, EndDate DATETIME  )

INSERT INTO #tmpDates (StartDate, EndDate)VALUES(   '2018-08-10', '2018-08-15')
GO

WITH myDates AS
(SELECT StartDate as aDate FROM #tmpDates AS td 
 UNION ALL
 SELECT DATEADD(day, 1, aDate) AS aDate FROM myDates 
 INNER JOIN #tmpDates AS td ON myDates.aDate >= td.StartDate
 WHERE DATEADD(day, 1, aDate) <= td.EndDate)
SELECT aDate, 1 as aValue
FROM myDates
INNER JOIN (SELECT StartDate, EndDate FROM #tmpDates AS td ) AS i ON 1=1
OPTION (MAXRECURSION 0);

DROP TABLE #tmpDates