Find Missing Dates in List

In the attached list there is a column of stores and a column of dates. The dates represent the FirstDayOfWeek when the week is a Sat-Fri.

Some stores have missing dates where the store was not open for various reasons.

How can I show a list that displays the store and the date that it's missing in SQL?
MissingDates.xlsx
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
A calendar table would be a option. The script below creates the calendar table using CTE, cross joins this table with the distinct store numbers, and then excludes the matches.
DECLARE @Test TABLE
(
	StoreNumber INT,
	FirstDayofWeek DATETIME
)

DECLARE @StartDate DATETIME = '2017-05-06', @EndDate DATETIME = '2017-06-10'

INSERT INTO @Test ( StoreNumber, FirstDayofWeek )
VALUES (1, '2017-05-06'), (1, '2017-05-13'), (1, '2017-05-20'), (1, '2017-05-27'), (1, '2017-06-03'), (1, '2017-06-10'),
	   (2, '2017-05-06'),                    (2, '2017-05-20'),                                       (2, '2017-06-10'),
	   (3, '2017-05-06'), (3, '2017-05-13'),                                       (3, '2017-06-03'), (3, '2017-06-10'),
	   (4, '2017-05-06'),                                                                             (4, '2017-06-10'),
       (5, '2017-05-06'), (5, '2017-05-13'),                    (5, '2017-05-27'), (5, '2017-06-03'), (5, '2017-06-10'),
	   (6, '2017-05-06'),
	                                                                                                  (7, '2017-06-10')

SELECT *
FROM @Test
ORDER BY StoreNumber, FirstDayofWeek

-- Create a Calendar table using CTE
;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100
e4(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) - 1 FROM e3),
e5(n) AS (SELECT DATEADD(WEEK, n, @StartDate) FROM e4)
-- End Calendar table creation
SELECT Stores.StoreNumber, e5.n MissingDates
FROM (SELECT DISTINCT StoreNumber FROM @Test) Stores
	CROSS JOIN e5
WHERE e5.n <= @EndDate
EXCEPT -- Use EXCEPT clause to remove matches
SELECT StoreNumber, FirstDayofWeek
FROM @Test

Open in new window

0
 
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
I can work with that. Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.