Group based on start and end dates

  • Hi, I have the following table:

    DECLARE @Employee TABLE (
    EmpNumber INT
    , MgrEmpNumber INT
    , Position VARCHAR(25)
    , StartDate DATE
    , EndDate DATE
    );

    INSERT INTO @Employee (
    EmpNumber
    , MgrEmpNumber
    , Position
    , StartDate
    , EndDate
    )
    VALUES
    (101, 4005, 'Developer', '2020-01-01', '2020-01-30')
    , (101, 4005, 'Developer', '2020-01-31', '2020-02-17')
    , (101, 4005, 'DBA', '2020-02-18', '2020-02-20')
    , (101, 4005, 'Developer', '2020-02-21', '2020-02-25')
    , (101, 5555, 'Engineer', '2020-02-27', '2020-03-06')
    , (101, 5555, 'Developer', '2020-03-07', '2020-03-17')
    , (101, 5555, 'DBA', '2020-04-21', NULL);

    Select * From @Employee;

    Expected result:

    Select 101 AS EmpNumber, 4005 AS MgrEmpNumber, 'Developer' AS Position, '2020-01-01' AS StartDate, '2020-02-17' AS EndDate
    UNION ALL
    Select 101 , 4005 , 'DBA' , '2020-02-18' , '2020-02-20'
    UNION ALL
    Select 101 , 4005 , 'Developer' , '2020-02-21' , '2020-02-25'
    UNION ALL
    Select 101 , 5555 , 'Engineer' , '2020-02-27' , '2020-03-06'
    UNION ALL
    Select 101 , 5555 , 'Developer' , '2020-03-07' , '2020-03-17'
    UNION ALL
    Select 101 , 5555 , 'DBA' , '2020-04-21' , NULL;

    I need to do a group on EmpNumber, MgrEmpNumber, and Position and show Min(StartDate) and Max(EndDate) if the StartDate is 1 day after the EndDate. An employee can be a developer under a manager for a period, change to DBA and be back to developer.

    I'd be thankful if you can please guide me with this.

  • If you need to look at previous or next records in a set, use LAG() or LEAD() with an OVER clause. then you can use conditional logic to see if they fit your criteria.

    maybe the easiest way is with a CTE.

  • pietlinden wrote:

    If you need to look at previous or next records in a set, use LAG() or LEAD() with an OVER clause. then you can use conditional logic to see if they fit your criteria.

    maybe the easiest way is with a CTE.

    I've tried using the window functions, but couldn't quite figure out which columns to use in the PARTITION clause. If I use Position along with Emp and Mgr, it's ignoring the dates.

  • Totally missed it... sounds like the classic Islands problem. (Clump all contiguous spans together).

    SQL Server Window Functions Gaps and Islands Problem (mssqltips.com)

     

  • Different ways to achieve it.  Depends on number of employees and its role under different managers with their start date.

    Try this:

    WITH cte1
    AS
    (SELECT
    EmpNumber
    ,MgrEmpNumber
    ,Position
    ,Startdate
    ,EndDate
    ,LEAD(position) OVER (PARTITION BY EmpNumber ORDER BY StartDate) Lead_position
    ,LAG(Startdate) OVER (PARTITION BY EmpNumber ORDER BY StartDate) Lag_startdate
    ,ROW_NUMBER() OVER (PARTITION BY EmpNumber ORDER BY StartDate) row_num
    FROM @Employee)
    SELECT
    e1.EmpNumber
    ,e1.MgrEmpNumber
    ,e1.Position
    ,(CASE
    WHEN e1.position = e2.position THEN e1.lag_startdate
    ELSE e1.startdate
    END) AS Startdate
    ,e1.EndDate
    FROM cte1 e1
    INNER JOIN cte1 e2
    ON e1.row_num = e2.row_num + 1
    ORDER BY e1.Empnumber, e1.StartDate

    =======================================================================

  • Thanks for your hint! I used a calendar table and DENSE_RANK() as shown in the article to solve my problem. I appreciate your help very much.

  • Sam Vanga wrote:

    Thanks for your hint! I used a calendar table and DENSE_RANK() as shown in the article to solve my problem. I appreciate your help very much.

    To help others that may view this thread, can you post your code, Sam?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply