SQL返回缺失的行

Question

我有以下架构，我对其进行了稍微简化:

CREATE TABLE [dbo].[Header] (
    [HeaderId] [int] IDENTITY(1,1) NOT NULL,
    [StaffId] [int] NOT NULL,
    [WeekEnding] [smalldatetime] NOT NULL,
    ...
)

CREATE TABLE [dbo].[Staff] (
    [StaffId] [int] NOT NULL,
    [FirstWeekEnding] [smalldatetime] NULL,
    ...
)

Header 表中的 StaffId 是外键。

标题表跟踪与员工相关的数据(在未显示的子表中)，如果子表中存在数据，则在给定的一周内将有一个“周末”条目。在这种情况下，“周末”始终是星期日。因此，示例数据可能如下所示:

HeaderId    StaffId    WeekEnding
---------------------------------
1           1          13/02/2011
2           1          20/02/2011
etc...

员工表中的“FirstWeekEnding”值是他们开始跟踪“标题”表中的信息的第一个日期。

我的问题

给定每个工作人员的第一周结束日期，如何构建一个查询来提供标题表中截至当前日期的所有缺失记录？

例如，给定以下数据:

StaffId     FirstWeekEnding
---------------------------
1           02/01/2011

HeaderId    StaffId    WeekEnding
---------------------------------
1           1          02/01/2011
2           1          09/01/2011
3           1          16/01/2011
4           1          13/02/2011
5           1          20/02/2011

结果将是:

StaffId    WeekEnding
---------------------
1          23/01/2011
1          30/01/2011
1          06/02/2011

理想情况下，查询应处理多个工作人员，并按其 StaffId 分组。

Answer 1

您将需要一种生成日期系列的方法。例如，参见http://syntaxhelp.com/SQLServer/Recursive_CTE 。然后搜索日期系列中没有匹配记录的条目。

DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = '2011-01-02', @endDate = GETDATE()

;WITH DateSeries AS (
    SELECT @startDate AS dt
    UNION ALL
    SELECT dt + 7 FROM DateSeries      -- using 7 for weekly interval
    WHERE dt + 7 <= @endDate
)
SELECT
    *
FROM
    DateSeries ds
LEFT JOIN
    (your data here) t
ON
    ds.dt = t.WeekEnding
WHERE
    t.WeekEnding IS NULL