T-SQL - Filtered Dates Between Range

Question

code:

DECLARE @T1 TABLE ([ID] INT, [WD] DATE, [SD] DATETIME, [ED] DATETIME, [Val] INT) INSERT INTO @T1 ( [ID], [WD], [SD], [ED], [VAL] ) VALUES ( 1, '2016-02-14', '2016-02-14 08:00:00', '2016-02-14 16:30:00', 12 ) ,( 1, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 15 ) ,( 3, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 12 ) ,( 1, '2016-02-21', '2016-02-22 08:00:00', '2016-02-22 16:30:00', 100 ) ,( 2, '2016-02-21', '2016-02-25 08:00:00', '2016-02-25 16:30:00', 124 ) ,( 3, '2016-03-20', '2016-03-21 08:00:00', '2016-03-21 16:30:00', 10 ) ,( 3, '2016-04-17', '2016-04-17 08:00:00', '2016-04-17 16:30:00', 8 ); DECLARE @StartDate DATE , @EndDate DATE; SELECT @StartDate = MIN(WD) FROM @T1 SELECT @EndDate = DATEADD(DAY,6,MAX(WD)) FROM @T1; DECLARE @T2 TABLE (D DATE) -- Table generated to get ALL dates between the minium WD (Min Sunday) and 6 days after the maximum WD (Max Sunday + 6 days to complete the week date range) INSERT INTO @T2 ( [D] ) SELECT DATEADD(DAY, NBR - 1, @StartDate) FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS NBR FROM sys.columns c ) NBR WHERE NBR - 1 <= DATEDIFF(DAY, @StartDate, @EndDate); SELECT * FROM @T1; SELECT * FROM @T2; 

Description: @ T1 is a table of filled source data with specific dates, where [ID] = an identifier field of some type, [WD] = different start of the week (on Sundays), [SD] = start of the day inside this [WD] week , [ED] = End of day during the week [WD], [Val] = Some assigned value for this record ID / WD / SD / ED

@ T2 is a table that I define and populate to get all dates within MIN ([WD]) and MAX ([WD]) + 6 days (to delay a week)

Goal:

Return @ T1 data along with missing dates for these [ID] / [WD] weeks. (see "Desired Result" for a better understanding)

Desired result (copied from Excel, so the format is different, but this is normal)

 [ID][WD] [SD] [ED] [Val] 1 2/14/2016 2/14/16 8:00 2/14/16 16:30 12 1 2/14/2016 2/15/16 00:00 2/15/16 00:00 NULL 1 2/14/2016 2/16/16 8:00 2/16/16 16:30 15 1 2/14/2016 2/17/16 00:00 2/17/16 00:00 NULL 1 2/14/2016 2/18/16 00:00 2/18/16 00:00 NULL 1 2/14/2016 2/19/16 00:00 2/19/16 00:00 NULL 1 2/14/2016 2/20/16 00:00 2/20/16 00:00 NULL 3 2/14/2016 2/14/16 00:00 2/14/16 00:00 NULL 3 2/14/2016 2/15/16 00:00 2/15/16 00:00 NULL 3 2/14/2016 2/16/16 8:00 2/16/16 16:30 12 3 2/14/2016 2/17/16 00:00 2/17/16 00:00 NULL 3 2/14/2016 2/18/16 00:00 2/18/16 00:00 NULL 3 2/14/2016 2/19/16 00:00 2/19/16 00:00 NULL 3 2/14/2016 2/20/16 00:00 2/20/16 00:00 NULL 1 2/21/2016 2/21/16 00:00 2/21/16 00:00 NULL 1 2/21/2016 2/22/16 8:00 2/22/16 16:30 100 1 2/21/2016 2/23/16 00:00 2/23/16 00:00 NULL 1 2/21/2016 2/24/16 00:00 2/24/16 00:00 NULL 1 2/21/2016 2/25/16 00:00 2/25/16 00:00 NULL 1 2/21/2016 2/26/16 00:00 2/26/16 00:00 NULL 1 2/21/2016 2/27/16 00:00 2/27/16 00:00 NULL 2 2/21/2016 2/21/16 00:00 2/21/16 00:00 NULL 2 2/21/2016 2/22/16 00:00 2/22/16 00:00 NULL 2 2/21/2016 2/23/16 00:00 2/23/16 00:00 NULL 2 2/21/2016 2/24/16 00:00 2/24/16 00:00 NULL 2 2/21/2016 2/25/16 8:00 2/25/16 16:30 124 2 2/21/2016 2/26/16 00:00 2/26/16 00:00 NULL 2 2/21/2016 2/27/16 00:00 2/27/16 00:00 NULL 3 3/20/2016 3/20/16 00:00 3/20/16 00:00 NULL 3 3/20/2016 3/21/16 8:00 3/21/16 16:30 10 3 3/20/2016 3/22/16 00:00 3/22/16 00:00 NULL 3 3/20/2016 3/23/16 00:00 3/23/16 00:00 NULL 3 3/20/2016 3/24/16 00:00 3/24/16 00:00 NULL 3 3/20/2016 3/25/16 00:00 3/25/16 00:00 NULL 3 3/20/2016 3/26/16 00:00 3/26/16 00:00 NULL 3 4/17/2016 4/17/16 8:00 4/17/16 16:30 8 3 4/17/2016 4/18/16 00:00 4/18/16 00:00 NULL 3 4/17/2016 4/19/16 00:00 4/19/16 00:00 NULL 3 4/17/2016 4/20/16 00:00 4/20/16 00:00 NULL 3 4/17/2016 4/21/16 00:00 4/21/16 00:00 NULL 3 4/17/2016 4/22/16 00:00 4/22/16 00:00 NULL 3 4/17/2016 4/23/16 00:00 4/23/16 00:00 NULL 

I tried using cross-connect and stuff, but no luck getting the desired result.

Answer 1

I deleted your @ T2 and used my @ T3 instead.

 DECLARE @T1 TABLE ([ID] INT, [WD] DATE, [SD] DATETIME, [ED] DATETIME, [Val] INT) INSERT INTO @T1 ( [ID], [WD], [SD], [ED], [VAL] ) VALUES ( 1, '2016-02-14', '2016-02-14 08:00:00', '2016-02-14 16:30:00', 12 ) ,( 1, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 15 ) ,( 3, '2016-02-14', '2016-02-16 08:00:00', '2016-02-16 16:30:00', 12 ) ,( 1, '2016-02-21', '2016-02-22 08:00:00', '2016-02-22 16:30:00', 100 ) ,( 2, '2016-02-21', '2016-02-25 08:00:00', '2016-02-25 16:30:00', 124 ) ,( 3, '2016-03-20', '2016-03-21 08:00:00', '2016-03-21 16:30:00', 10 ) ,( 3, '2016-04-17', '2016-04-17 08:00:00', '2016-04-17 16:30:00', 8 ); DECLARE @T3 TABLE ([ID] INT, [WD] DATE, [SD] DATETIME, [ED] DATETIME); WITH cte AS ( SELECT DISTINCT ID, WD FROM @T1 ) INSERT INTO @T3 (ID, WD, SD, ED) SELECT ID, WD, DATEADD(DAY,n,WD), DATEADD(DAY,n,WD) FROM cte CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6)) AS ADDED(n); SELECT t3.ID, t3.WD,COALESCE(t1.SD,t3.SD) AS SD, COALESCE(t1.ED,t3.ED) AS ED, t1.Val FROM @T3 t3 LEFT JOIN @T1 t1 ON t3.ID=t1.ID AND CONVERT(DATE,t1.SD)=CONVERT(DATE,t3.SD) 

Answer 2

This will be the left connection of your source data with the timeline you created.

I could not understand how the identifier is assigned, but when I use COALESCE to fill in the missing values, this returns the desired result (except for the ID):

 SELECT [t1].[ID] ,COALESCE([t1].[WD], DATEADD(DAY, 1 - DATEPART(WEEKDAY, [t2].[D]), [t2].[D])) AS WD ,COALESCE([t1].[SD], [t2].[D]) AS SD ,COALESCE([t1].[ED], [t2].[D]) AS ED ,[t1].[Val] FROM @T2 [t2] LEFT JOIN @T1 [t1] ON [t2].[D] = CONVERT(DATE, [t1].[SD]);