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