SQL to return missing rows

Question

I have the following diagram, which I simplified a bit:

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 in the header table is a foreign key.

The heading table tracks personnel-related data (not shown in the subcategory), and for that week there will be an entry for the "end of the week" if there is data in the auxiliary table. In this case, the "end of the week" is always Sunday. Therefore, sample data may look like this:

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

The FirstWeekEnding value in the staff table is the first start date for tracking information in the header table.

My question

Given the end date of the first week of each employee, how do I build a query that will give me all the MISSING entries from the header table to the current 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 

Result:

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

Ideally, the request should be handled by several employees grouped by their StaffId.

Answer 1

Assuming you are using SQL 2005+, you can generate all weeks for each employee and try to join the header table in CTE (something like, but maybe not quite like that):

 ;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 create a series of dates. See for example http://syntaxhelp.com/SQLServer/Recursive_CTE . Then find entries that do not have a matching entry 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 it.

Here's the last request:

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