SQL to return missing rows

Question

I have the following schema, which I've simplified slightly:

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,
    ...
)

StaffId on the Header table is a foreign key.

The Header table tracks data related to staff (in a sub table not shown) and for a given week there will be an entry for the "week end" if data in the sub table exists. In this case the "week end" is always a Sunday. Therefore, sample data could look like this:

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

The "FirstWeekEnding" value on the staff table is the first date they started tracking information in the Header table.

My Question

Given the first week ending date of each staff member, how do I construct a query that will give me all the MISSING records from the Header table up to the present date?

For example, given the following data:

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

The result would be:

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

Ideally the query should handle multiple staff members, grouped by their StaffId.

Answer 1

Assuming you're using SQL 2005+ you could generate all the weeks for each staff and attempt to join against the Header table in a CTE (something like, but perhaps not exactly this):

;WITH cte AS 
( SELECT StaffId, FirstWeekEnding AS WeekEnding
  FROM STAFF
  UNION ALL
  SELECT StaffId, DATEADD(D, 7, WeekEnding) FROM cte
  WHERE DATEADD(D, 7, WeekEnding) <= GETDATE()
)
SELECT StaffId, WeekEnding
FROM cte LEFT JOIN Header ON cte.StaffId = Header.StaffId AND cte.WeekEnding = Header.WeekEnding
WHERE Header.WeekEnding IS NULL
OPTION (MAXRECURSION 32767)

Answer 2

You will need a way to generate a date series. See for example http://syntaxhelp.com/SQLServer/Recursive_CTE. Then search for entries where there is no matching record in the date series.

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

Answer 3

OK so i suggest creating a Calendar table and joining on that.

Here's the final query:

select StaffID, BaseDate
From (
select 
 StaffID, 
 BaseDate, 
 (
  Select count(*) 
  from Header h 
  where h.WeekEnding = c.BaseDate And h2.StaffID = h.StaffID
 ) as Count
From Header h, Calendar c ) as Subquery
Where Count = 0

And the Calendar:

create table Calendar (
BaseDate datetime primary key,
DayOfWeek varchar(10) not null,
WeekOfYear int not null,
MonthOfYear varchar(10) not null,
Quarter int not null
/* Add any other useful columns */
)
go

declare @d datetime
set @d = '20090101'
while @d < '20250101'
begin
insert into dbo.Calendar values (
@d,
datename(dw, @d),
datepart(ww, @d),
datename(mm, @d),
datepart(qq, @d))
set @d = dateadd(dd, 1, @d)
end
go

select *
from dbo.Calendar
where DayOfWeek = 'Sunday' and
BaseDate between '20090101' and '20250101'
go