SQL 来识别缺失的一周

Question

我有一个具有以下结构的数据库表 -

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

Week_End 是一个日期时间列，并且每个新条目的日期都会增加 7 天。

我想要的是一个 SQL 语句，该语句将确定序列中是否缺少一周。因此，如果表包含以下数据 -

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

查询将返回 2009-11-15。

这可能吗？顺便说一句，我正在使用 SQL Server 2008。

Answer 1

您已经接受了答案，所以我想您不需要这个，但无论如何我都快完成了它，它有一个所选解决方案所没有的优点：它不需要每年更新。它是这样的：

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)

它基于 Andemar 的解决方案，但也处理年份的变化，并且不需要 Sales 列的存在。

Answer 2

连接表本身以搜索连续行：

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
)

这应该返回没有下一周的任何周。

Answer 3

假设您的“week_end”日期始终是一周中的星期日，您可以尝试使用 CTE - 一个通用表表达式，列出 2009 年的所有星期日，然后对您的表执行外连接。

表中缺少的所有行在选择中的“week_end”将具有 NULL 值：

;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 4

我知道这个问题已经得到了回答，但是我可以提出一些非常简单的建议吗？

/* 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)
;

如果您知道要查看的范围，则无需在 CTE 中使用 MIN/MAX 子查询。