SQL to identify missing week

Question

I have a database table with the following structure -

Week_End      Sales
2009-11-01    43223.43
2009-11-08     4324.23
2009-11-15    64343.23
...

Week_End is a datetime column, and the date increments by 7 days with each new entry.

What I want is a SQL statement that will identify if there is a week missing in the sequence. So, if the table contained the following data -

Week_End      Sales
2009-11-01    43223.43
2009-11-08     4324.23
2009-11-22    64343.73    
...

The query would return 2009-11-15.

Is this possible? I am using SQL Server 2008, btw.

Answer 1

Join the table on itself to search for consecutive rows:

select a.*
from YourTable a
left join YourTable b
    on datepart(wk,b.Week_End) = datepart(wk,a.Week_End) + 1
-- No next week
where b.sales is null 
-- Not the last week
and datepart(wk,a.Week_End) <> (
    select datepart(wk,max(Week_End)) from YourTable
)

This should return any weeks without a next week.

Answer 2

Assuming your "week_end" dates are always going to be the Sundays of the week, you could try a CTE - a common table expression that lists out all the Sundays for 2009, and then do an outer join against your table.

All those rows missing from your table will have a NULL value for their "week_end" in the select:

;WITH Sundays2009 AS
(
    SELECT CAST('20090104' AS DATETIME) AS Sunday
    UNION ALL
    SELECT  
       DATEADD(DAY, 7, cte.Sunday)
    FROM  
       Sundays2009 cte 
    WHERE  
       DATEADD(DAY, 7, cte.Sunday) < '20100101'
)
SELECT
    sun.Sunday 'Missing week end date'
FROM
    Sundays2009 sun
LEFT OUTER JOIN     
    dbo.YourTable tbl ON sun.Sunday = tbl.week_end
WHERE
    tbl.week_end IS NULL

Answer 3

You've already accepted an answer so I guess you don't need this, but I was almost finished with it anyway and it has one advantage that the selected solution doesn't have: it doesn't require updating every year. Here it is:

SELECT T1.*
FROM Table1 T1
LEFT JOIN Table1 T2
ON T2.Week_End = DATEADD(week, 1, T1.Week_End)
WHERE T2.Week_End IS NULL
AND T1.Week_End <> (SELECT MAX(Week_End) FROM Table1)

It is based on Andemar's solution, but handles the changing year too, and doesn't require the existence of the Sales column.

Answer 4

I know this has already been answered, but can I suggest something really simple?

/* First make a list of weeks using a table of numbers (mine is dbo.nums(num),  starting with 1) */
WITH AllWeeks AS (
  SELECT DATEADD(week,num-1,w.FirstWeek) AS eachWeek
  FROM 
    dbo.nums
    JOIN
    (SELECT MIN(week_end) AS FirstWeek, MAX(week_end) as LastWeek FROM yourTable) w
    ON num <= DATEDIFF(week,FirstWeek,LastWeek)
)
/* Now just look for ones that don't exist in your table */
SELECT w.eachWeek AS MissingWeek
FROM AllWeeks w
WHERE NOT EXISTS (SELECT * FROM yourTable t WHERE t.week_end = w.eachWeek)
;

If you know the range you want to look over, you don't need to use the MIN/MAX subquery in the CTE.