SQL to identify missing week

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    
..开发者_C百科.

The query would return 2009-11-15.

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

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.

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.

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

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.