Sql-server – Find termination and rehire dates

gaps-and-islandssql server

We have a time history table named PRTH that has EmployeeID, PostDate (this is the day worked), and hours. Our ERP system does not track termination and rehire date history so I have been asked to look at the PRTH table to determine when an employee was terminated and rehired based on days without hours.

For example, if the last time I had hours was 1-1-17 and I had hours again on 3-1-17 then it should show my term date as 1-1-17 and rehire as 3-1-17. This appears to be an island and gap issue but I am drawing a blank.

    SELECT Employee, PostDate
    FROM dbo.PRTH 
    WHERE Employee BETWEEN 400 AND 450
    ORDER BY Employee, PostDate 

Employee    PostDate
400 2013-09-30 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-15 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-30 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00
400 2013-10-31 00:00:00

The sample data above should be divided into 3 groups: (2013-09-30, 2013-09-30), (2013-10-15, 2013-10-15) and (2013-10-30, 2013-10-31). If employee 400 worked 1-1-2015 and the next date he worked was 5-1-2016 then we would want to see emp 400, term 1-1-2015, hired 5-1-16. Then if he had another gap in time history we would want another line with those and so on throughout all of our employees.

I have created a master calendar and tried the below statement but it has millions of rows. If I could only capture each term and end date instead of every day without pay that would be great.

WITH CTE_GAPS AS
(SELECT Employee,
    LAG(P.PostDate) Over(Partition by P.Employee Order By    P.Employee,P.PostDate) GapStart ,
    PostDate GapEnd,
    (DATEDIFF(DAY,LAG(P.PostDate) Over(Partition by P.Employee Order By P.Employee,P.PostDate),P.PostDate)-1) GapDays
FROM dbo.PRTH P)

SELECT  P.Employee,
    C.Calendar_Date
FROM Master.dbo.Calendar C CROSS JOIN CTE_GAPS P
WHERE
GapDays>29 AND
C.Calendar_Date
    BETWEEN DATEADD(DAY,1,P.GapStart ) AND DATEADD(DAY,-1,P.GapEnd)
    GROUP BY P.Employee, P.GapStart, C.Calendar_Date

Best Answer

I believe I was right there at the beginning. Here is what I ended up using. Please advise if anyone sees any improvements. Thanks!

WITH CTE_GAPS AS
(SELECT P.Employee, pp.FirstName, pp.LastName,
    LAG(P.PostDate) Over(Partition by P.Employee Order By 
P.Employee,P.PostDate) GapStart ,
    PostDate GapEnd,
    (DATEDIFF(DAY,LAG(P.PostDate) Over(Partition by P.Employee Order By 
P.Employee,P.PostDate),P.PostDate)-1) GapDays
FROM dbo.PRTH P INNER JOIN dbo.PREH pp ON P.PRCo = pp.PRCo AND P.Employee = 
pp.Employee)

SELECT  P.Employee,
P.FirstName,
P.LastName,
P.GapStart,
P.GapEnd
FROM Master.dbo.Calendar C CROSS JOIN CTE_GAPS P
WHERE
GapDays>29 AND
C.Calendar_Date
    BETWEEN DATEADD(DAY,1,P.GapStart ) AND DATEADD(DAY,-1,P.GapEnd)
    GROUP BY P.Employee, P.GapStart, P.GapEnd, P.FirstName, P.LastName
Related Question