Have Geek Brain

Select Statement to show missing records (easy question)

I need a T-SQL that will show the missing records.

Here are 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 validation range will start from today and go back 6 months.

In this example, let's say today is 06/12/2010, so the range would be 01/01/2010 to 06/01/2010.

The day will always be the first in the data.

Thanks a bunch. :)

Gerhard Weiss
Secretary Great Lakes Area.NET User Group


Upcoming GANG Meetings | GANG LinkedIn Group

+2


a source to share


3 answers


Try the following:

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 be doing every day for the past six months, just include this in the above if that's 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
+2


a source


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



+1


a source


If you only go back for fixed months, you can pre-define the "first month" dates and left join with 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
+1


a source






More articles:


All Articles