Insert Missing Date row for each group With & W/O using CTE

Question

This is my calendar table:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '9/24/2018';
SET @EndDate = '9/28/2018';
WITH theDates AS
 (SELECT @StartDate AS theDate
 UNION ALL
 SELECT DATEADD(DAY, 1, theDate)
 FROM theDates
 WHERE DATEADD(DAY, 1, theDate) <= @EndDate 
 )
INSERT INTO dbo.tblDateRangeNBNO
SELECT theDate FROM theDates
OPTION (MAXRECURSION 0);

I need to join this to the another table to get each Group's (Values to be carried from same group) missing rows for that date.

Another table:

MeetRoom | StartDT | Status | MinsUsed |
 tpa 2018年09月25日 08:15:00.000 IN 50
 tpa 2018年09月26日 08:35:00.000 FL 90
 ngf 2018年09月24日 08:00:00.000 TN 600
 ngf 2018年09月27日 17:56:31.563 MN 210
 pdb 2018年09月24日 02:11:00.000 TL 200
 pdb 2018年09月28日 14:54:46.473 TD 150

Expected result set:

'FC' will be the Status & 1440 will be MinsUsed for each missing MeetRoom dates

MeetRoom | StartDT | Status | MinsUsed |
 tpa 2018年09月24日 00:00:00.000 FC 1440
 tpa 2018年09月27日 00:00:00.000 FC 1440
 tpa 2018年09月28日 00:00:00.000 FC 1440
 ngf 2018年09月25日 00:00:00.000 FC 1440
 ngf 2018年09月26日 00:00:00.000 FC 1440
 ngf 2018年09月28日 00:00:00.000 FC 1440
 pdb 2018年09月25日 00:00:00.000 FC 1440
 pdb 2018年09月26日 00:00:00.000 FC 1440
 pdb 2018年09月27日 00:00:00.000 FC 1440

I've the following query, but skipping few records (not sure whats the missing piece)

SELECT p.DateRange, COALESCE(bi.[Status], 'FC') [Status], p.MeetRoom
 FROM
 (
 SELECT MeetRoom, DateRange
 FROM
 (
 SELECT MeetRoom, MIN(DateRange) Min_Date, MAX(DATEADD(DAY, 1, DateRange)) Max_Date
 FROM tblBI b RIGHT JOIN tblDateRangeNBNO d ON CAST(d.DateRange AS DATE) = CAST(b.StartDT AS DATE) 
 GROUP BY MeetRoom
 ) q CROSS JOIN tblDateRangeNBNO dr
 WHERE dr.DateRange BETWEEN q.min_date AND DATEADD(DAY, 1, q.max_date)
 ) p LEFT JOIN tblBI bi
 ON p.MeetRoom= bi.MeetRoom AND CAST(p.DateRange AS DATE) = CAST(bi.StartDT AS DATE)
 WHERE bi.[Status] IS NULL

Answer 1

To get each day in the range:

DECLARE @StartDate date = '20180924', -- stay away from m/d/y!
 @EndDate date = '20180928';
;WITH n(n) AS 
(
 SELECT 0 UNION ALL SELECT n + 1 FROM n 
 WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
), d(d) AS 
(
 SELECT DATEADD(DAY, n, @StartDate) FROM n
)
SELECT d FROM d
OPTION (MAXRECURSION 0);

This returns:

d
----------
2018年09月24日
2018年09月25日
2018年09月26日
2018年09月27日
2018年09月28日

Now to produce a row for every possible meeting room on every date, you cross join against the distinct set of meeting rooms present within the range, and then left outer join that set against your data (I'm not sure why you are inserting your date range into a permanent table, this isn't necessary and it kills concurrency).

DECLARE @StartDate date = '20180924',
 @EndDate date = '20180928';
;WITH n(n) AS 
(
 SELECT 0 UNION ALL SELECT n + 1 FROM n 
 WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
), d(d) AS 
(
 SELECT DATEADD(DAY, n, @StartDate) FROM n
),
roomdata AS 
(
 SELECT MeetRoom, StartDT, status, minsused 
 FROM dbo.tblBI
 WHERE StartDT >= @StartDate
 AND StartDT < DATEADD(DAY, 1, @EndDate)
),
rooms AS
(
 SELECT DISTINCT MeetRoom FROM roomdata 
)
SELECT r.MeetRoom, d.d, status = COALESCE(rd.status, 'FC'),
 minsused = COALESCE(rd.minsused, 1440)
FROM d
CROSS JOIN rooms AS r 
LEFT OUTER JOIN roomdata AS rd
ON r.MeetRoom = rd.MeetRoom
AND rd.StartDT >= d.d
AND rd.StartDT < DATEADD(DAY, 1, d.d)
ORDER BY r.MeetRoom, d.d
OPTION (MAXRECURSION 0);

Currently this returns the rows that exist and a row for each combination that doesn't exist. If what you want is just the set of rows that don't exist, then just add a WHERE clause before the ORDER BY:

...
AND rd.StartDT < DATEADD(DAY, 1, d.d)
WHERE rd.status IS NULL
ORDER BY r.MeetRoom, d.d;

• SATISD9X: Worked! Thanks .. another query - Is there a possible effective query to fetch the same results without using CTE
• Aaron Bertrand: For what purpose? You could use sub-queries I suppose, but it wouldn't change performance, it would just make it harder to read IMHO. So, what is the problem with a CTE?
• SATISD9X: Just a workaround for same query ... cos I've to use this same to insert into the same table ... I will change the syntax to suit it.. Thanks
• Aaron Bertrand: You just put your insert in between: ;WITH CTE AS (...cte...) INSERT ... SELECT ... FROM cte;
• DJA: Warning, if you span more than 100 days then you get Msg 530, Level 16, State 1, Line 16 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Answer 2

This works, but testing is required for different scenario's / datasets. There could also be a better way to do this, for example a faster performing one.

DECLARE @tblDateRangeNBNO table (theDate DATETIME2)
DECLARE @AnotherTable table (MeetRoom varchar(3),
 StartDT DATETIME2 ,
 Status varchar(2),
 MinsUsed int)
insert into @AnotherTable 
VALUES ('tpa ', '2018-09-25 08:15:00.000' ,'IN ' , 50 ),
 ('tpa ', '2018-09-26 08:35:00.000' ,'FL ', 90 ),
 ('tpa ', '2018-09-26 08:35:00.000' ,'FL ', 90 ),
 ('ngf ', '2018-09-24 08:00:00.000' ,'TN ' , 600 ),
 ('ngf ', '2018-09-27 17:56:31.563' ,'MN ' , 210 ),
 ('pdb ', '2018-09-24 02:11:00.000' ,'TL ' , 200 ),
 ('pdb ', '2018-09-28 14:54:46.473' ,'TD ' , 150 );
DECLARE @StartDate DATETIME2;
DECLARE @EndDate DATETIME2;
SET @StartDate = '9/24/2018';
SET @EndDate = '9/28/2018';
WITH theDates AS
 (SELECT @StartDate AS theDate
 UNION ALL
 SELECT DATEADD(DAY, 1, theDate)
 FROM theDates
 WHERE DATEADD(DAY, 1, theDate) <= @EndDate 
 )
INSERT INTO @tblDateRangeNBNO
SELECT theDate FROM theDates
OPTION (MAXRECURSION 0);
/** 
This example uses a cross join to get all the possible combinations,
afterwards the filtering happens, but because of all these possible combinations, too many results are returned.
To mitigate this, i am going to check for what the max values are using windowing functions.
Performance of this query will probably be bad on bigger datasets.
**/ 
SELECT theDate,MeetRoom, 'FC' as Status, 1440 as MinsUsed from
(
SELECT theDate,ANT.MeetRoom,count(*) as counting,max(count(*)) over( partition by meetroom) as counter2
FROM @tblDateRangeNBNO TBDR
CROSS JOIN
 @AnotherTable ANT
WHERE NOT EXISTS
 (
 select cast(ANT.StartDT as date)
 INTERSECT
 SELECT cast(TBDR.theDate as date)
 )
GROUP BY MeetRoom,theDate
) as A
WHERE counter2 = A.counting
ORDER BY MeetRoom DESC;

Result set:

theDate MeetRoom Status MinsUsed
2018年09月24日 00:00:00.0000000 tpa FC 1440
2018年09月27日 00:00:00.0000000 tpa FC 1440
2018年09月28日 00:00:00.0000000 tpa FC 1440
2018年09月25日 00:00:00.0000000 pdb FC 1440
2018年09月26日 00:00:00.0000000 pdb FC 1440
2018年09月27日 00:00:00.0000000 pdb FC 1440
2018年09月25日 00:00:00.0000000 ngf FC 1440
2018年09月26日 00:00:00.0000000 ngf FC 1440
2018年09月28日 00:00:00.0000000 ngf FC 1440