Identify gaps in date in employee table

• ken.trock

  SSCertifiable

  Points: 5147

  More actions

  February 9, 2017 at 9:57 am

  #334898

  T-SQL gurus, I'm trying to audit our EmployeeHistory table to see if there's gaps for EmployeeIDs in their start and end dates.

  USE master

  CREATE TABLE EmpHistory(EmployeeID INT, StartDate DATE, EndDate DATE)

  INSERT dbo.EmpHistory

  ( EmployeeID, StartDate, EndDate )

  VALUES (101039, '11/1/16', '11/2/16'),

  (101039, '11/3/16', '11/6/16'),

  --Here's a gap

  (101039, '11/10/16', '11/30/16'),

  (101039, '12/1/16', '12/13/16'),

  (101039, '12/14/16', '12/31/16'),

  --This EmployeeID is clean

  (101077, '11/1/16', '12/3/16'),

  (101077, '12/4/16', '12/5/16'),

  (101077, '12/6/16', '12/15/16'),

  (101077, '12/16/16', '12/21/16'),

  (101077, '12/21/16', '12/31/16')

  Given this, could someone help me figure out how to identify an EmployeeID and date range missing in their employement record? The result set for this should tell me 01039 is missing for 11/7/16 - 11/9/16. I have a generic Date table if needed. The set of data I'll be running this on is upwards of 30M rows so am hoping not to use a function 🙂

  Thanks a lot,
  Ken

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 9, 2017 at 10:05 am

  #1927581

  You should really post SQL that doesn't rely on User languages. i can't run the above as I'm British (there aren't 31 months in the year). Ideally supply dates in yyyymmdd format, as anyone can use it.

  This should work, but I haven't tested:
  WITH Employees AS (
  SELECT EH.*,
        LAG(EH.EndDate) OVER (PARTITION BY EH.EmployeeID ORDER BY EH.StartDate) as LastEndDate
  FROM EmpHistory EH)
SELECT *
FROM Employees E
WHERE E.LastEndDate != DATEADD(DAY, -1, E.StartDate);

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  February 9, 2017 at 10:14 am

  #1927584

  Here is one way of doing it:

  
with MyCTE as (
    select EmployeeID, StartDate, EndDate, LAG(EndDate, 1, NULL) OVER (PARTITION BY EmployeeID ORDER BY StartDate) as PrevEndDate
    from EmpHistory)
SELECT *, IIF(PrevEndDate IS NOT NULL AND DATEDIFF(DAY,PrevEndDate, StartDate) > 1, 'gap','clean')
FROM MyCTE


  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• ken.trock

  SSCertifiable

  Points: 5147

  More actions

  February 9, 2017 at 12:55 pm

  #1927634

  Thanks you guys for the queries. I hadn't thought of LAG(). My bad on the dates. Either one of these do the trick.

  Ken