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

Aaron Bertrand · Accepted Answer · 2020-04-10 01:57:21Z

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;

Worked! Thanks .. another query - Is there a possible effective query to fetch the same results without using CTE — SATISD9X, Nov 8 '18 at 15:12
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? — Aaron Bertrand, Nov 8 '18 at 15:13
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 — SATISD9X, Nov 8 '18 at 15:21
You just put your insert in between: ;WITH CTE AS (...cte...) INSERT ... SELECT ... FROM cte; — Aaron Bertrand, Nov 8 '18 at 15:24
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. — DJA, Apr 10 '20 at 1:36

Randi Vertongen · Accepted Answer · 2018-11-07 22:46:43Z

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

Stack Exchange Network

current community

your communities

more stack exchange communities

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

This is my calendar table:

Another table:

Expected result set:

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server sql-server-2012 or ask your own question.

Hot Network Questions

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

This is my calendar table:

Another table:

Expected result set:

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server sql-server-2012 or ask your own question.

Related

Hot Network Questions