1

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
  • 2
    That's tricky to do, because even if you solve the island/gap issue, you've got another issue: Do you have any way to track vacation or sick time? If I'm on a 2-week vacation, an answer to this question would show that I was fired/rehired. Or if I was out on an extended sick leave or paternity/maternity leave... Does your ERP track those states? – CaM Aug 7 '17 at 13:05
  • 1
    Could you tag the question with the DBMS you're using (and the version, if possible)? – RDFozz Aug 7 '17 at 15:21
  • It looks like you've defined any gap greater than 29 days as reflecting a termination and rehire situation. Why exactly are you joining to the master calendar? I would think that simply taking the Employee, GapStart, and Gap End where the GapDays are more than 29 would be what you're looking for. Have you tried that? If so, why doesn't it give you what you need? Could you add in what you're expected output should be? – RDFozz Aug 7 '17 at 15:30
  • this line is not clear to me ,explain with example."Then if he had another gap in time history we would want another line with those and so on throughout all of our employees." – KumarHarsh Aug 8 '17 at 3:42
0

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  
0

Sample data

CREATE TABLE prth(employee, postdate)
AS VALUES
    ( 400::id, '2013-09-30 00:00:00'::date ),
    ( 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' );

Query

SELECT employee, min(postdate), max(postdate)
FROM (
        SELECT
                employee,
                postdate,
                count(is_reset OR NULL) OVER (PARTITION BY employee ORDER BY postdate) AS grp
        FROM (
                SELECT
                        employee,
                        postdate,
                        1 < postdate::date - lag(postdate) OVER (PARTITION BY employee ORDER BY postdate) AS is_reset
                FROM prth
                ORDER BY employee, postdate
        ) AS t1
) AS t2
GROUP BY employee, grp; 

Explanation

First we set resets,

SELECT
        employee,
        postdate,
        1 < postdate::date - lag(postdate) OVER (PARTITION BY employee ORDER BY postdate) AS is_reset
FROM prth
ORDER BY employee, postdate;

 employee |  postdate  | is_reset 
----------+------------+----------
      400 | 2013-09-30 | 
      400 | 2013-10-15 | t
      400 | 2013-10-15 | f
      400 | 2013-10-15 | f
      400 | 2013-10-15 | f
      400 | 2013-10-15 | f
      400 | 2013-10-30 | t
      400 | 2013-10-30 | f
      400 | 2013-10-30 | f
      400 | 2013-10-30 | f
      400 | 2013-10-31 | f
      400 | 2013-10-31 | f
      400 | 2013-10-31 | f
      400 | 2013-10-31 | f
      400 | 2013-10-31 | f
      400 | 2013-10-31 | f
      400 | 2013-10-31 | f
(17 rows)

Now we have to group them..

SELECT
        employee,
        postdate,
        count(is_reset OR NULL) OVER (PARTITION BY employee ORDER BY postdate) AS grp
FROM (
        SELECT
                employee,
                postdate,
                1 < postdate::date - lag(postdate) OVER (PARTITION BY employee ORDER BY postdate) AS is_reset
        FROM prth
        ORDER BY employee, postdate
) AS t;

 employee |  postdate  | grp 
----------+------------+-------
      400 | 2013-09-30 |     0
      400 | 2013-10-15 |     1
      400 | 2013-10-15 |     1
      400 | 2013-10-15 |     1
      400 | 2013-10-15 |     1
      400 | 2013-10-15 |     1
      400 | 2013-10-30 |     2
      400 | 2013-10-30 |     2
      400 | 2013-10-30 |     2
      400 | 2013-10-30 |     2
      400 | 2013-10-31 |     2
      400 | 2013-10-31 |     2
      400 | 2013-10-31 |     2
      400 | 2013-10-31 |     2
      400 | 2013-10-31 |     2
      400 | 2013-10-31 |     2
      400 | 2013-10-31 |     2
(17 rows)

That gives us what you want, now we can clearly GROUP BY and get min/max.

SELECT employee, min(postdate), max(postdate)
FROM (
        SELECT
                employee,
                postdate,
                count(is_reset OR NULL) OVER (PARTITION BY employee ORDER BY postdate) AS grp
        FROM (
                SELECT
                        employee,
                        postdate,
                        1 < postdate::date - lag(postdate) OVER (PARTITION BY employee ORDER BY postdate) AS is_reset
                FROM prth
                ORDER BY employee, postdate
        ) AS t1
) AS t2
GROUP BY employee, grp;

 employee |    min     |    max     
----------+------------+------------
      400 | 2013-10-15 | 2013-10-15
      400 | 2013-09-30 | 2013-09-30
      400 | 2013-10-30 | 2013-10-31
(3 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.