Sql-server – Insert Missing Date row for each group With & W/O using CTE

sql serversql-server-2012

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

Best Answer

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;
Related Question