Select Statement to show missing records (Easy Question)

Question

I need some T-SQL that will show missing records.

Here is some sample data:

Emp 1
01/01/2010
02/01/2010
04/01/2010
06/01/2010

Emp 2
02/01/2010
04/01/2010
05/01/2010
etc...

I need to know

Emp 1 is missing 
03/01/2010
05/01/2010

Emp 2 is missing 
01/01/2010
03/01/2010 
06/01/2010

The range to check will start with todays date and go back 6 months.

In this example, lets say today's date is 06/12/2010 so the range is going to be 01/01/2010 thru 06/01/2010.

The day is always going to be the 1st in the data.

Thanks a bunch. :)

Gerhard Weiss
Secretary of Great Lakes Area .NET Users Group
GANG Upcoming Meetings | GANG LinkedIn Group

Answer 1

fill a temp table with the date ranges and outer join the temp table to your Emp* table and only return records from your temp table that have null in the corresponding row of the Emp* table

Answer 2

If you're only going back a fixed # of months, you can precalc those "first of month" dates and left join to your employee data:

SELECT d.DT, CASE WHEN e.DT IS NULL THEN 1 ELSE 0 END AS IsMissing
FROM (
    SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP), 0) AS DT
    UNION
    SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 1, 0)
    UNION
    SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 2, 0)
    UNION
    SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 3, 0)
    UNION
    SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 4, 0)
    UNION
    SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 5, 0)
) AS d
LEFT JOIN EmployeeDates e ON d.DT = e.DT AND e.EmpID = 1

Answer 3

Try This:

DECLARE @Employees table (DateOf datetime, EmployeeID int)
INSERT @Employees VALUES ('01/01/2010',1)
INSERT @Employees VALUES ('02/01/2010',1)
INSERT @Employees VALUES ('04/01/2010',1)
INSERT @Employees VALUES ('06/01/2010',1)
INSERT @Employees VALUES ('02/01/2010',2)
INSERT @Employees VALUES ('04/01/2010',2)
INSERT @Employees VALUES ('05/01/2010',2)

--I was unsure of the data in the question
--this gives first day of each month for last six months
DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETDATE()),0) )
      ,@EndDate=GETDATE()

;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DateAdd(month,1,DateOf)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT
    dt.DateOf,dt.EmployeeID
    FROM (SELECT DISTINCT
              a.DateOf,e.EmployeeID
          FROM AllDates                    a
              CROSS JOIN (SELECT DISTINCT EmployeeID FROM @Employees) e
        ) dt
        LEFT OUTER JOIN @Employees ee ON dt.EmployeeID=ee.EmployeeID AND dt.DateOf=ee.DateOf
    WHERE ee.EmployeeID IS NULL
    ORDER BY dt.EmployeeID,dt.DateOf

OUTPUT:

DateOf                  EmployeeID
----------------------- -----------
2009-10-01 00:00:00.000 1
2009-11-01 00:00:00.000 1
2009-12-01 00:00:00.000 1
2010-03-01 00:00:00.000 1
2010-05-01 00:00:00.000 1
2009-10-01 00:00:00.000 2
2009-11-01 00:00:00.000 2
2009-12-01 00:00:00.000 2
2010-01-01 00:00:00.000 2
2010-03-01 00:00:00.000 2

(10 row(s) affected)

this will do every day for last six months, just incorporate this in the above if that is what you want:

DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,GETDATE())
      ,@EndDate=GETDATE()
;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DateOf+1
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT * FROM AllDates
--OPTION (MAXRECURSION 500) --uncomment and increase if the date range needs more rows