Quick example of an inline calendar CTE, you should be able to adjust it to your requirements.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @DateRanges TABLE
(
StartDate DATE,
EndDate DATE
)
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-01','2016-12-01')--2016-12-01
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-01','2016-12-02')--2016-12-01 and 2016-12-02
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-04','2016-12-06')--2016-12-04, 2016-12-05, 2016-12-06
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-08','2016-12-11')--2016-12-08, 2016-12-09, 2016-12-10 and 2016-12-11
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-10','2016-12-12')--2016-12-10, 2016-12-11 and 2016-12-12
INSERT INTO @DateRanges (StartDate,EndDate) VALUES ('2016-12-15','2016-12-15')--2016-12-15
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
MIN(T.StartDate) AS FIRST_DATE
,DATEDIFF(DAY,MIN(T.StartDate),MAX(EndDate)) AS NUM_DAYS
FROM @DateRanges T
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC) + 1) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,DATEADD(DAY,NM.N,BC.FIRST_DATE) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
SELECT
IC.DATE_NO
,IC.DATE_VAL
,DR.StartDate
FROM INLINE_CALENDAR IC
LEFT OUTER JOIN @DateRanges DR
ON IC.DATE_VAL = DR.StartDate;
Output
DATE_NO DATE_VAL StartDate
-------- ---------- ----------
0 2016-12-01 2016-12-01
0 2016-12-01 2016-12-01
1 2016-12-02 NULL
2 2016-12-03 NULL
3 2016-12-04 2016-12-04
4 2016-12-05 NULL
5 2016-12-06 NULL
6 2016-12-07 NULL
7 2016-12-08 2016-12-08
8 2016-12-09 NULL
9 2016-12-10 2016-12-10
10 2016-12-11 NULL
11 2016-12-12 NULL
12 2016-12-13 NULL
13 2016-12-14 NULL
14 2016-12-15 2016-12-15