Group based on start and end dates

Question

Post reply

Group based on start and end dates

Sam Vanga

SSCommitted

Points: 1742

December 9, 2021 at 7:26 pm

#3962155

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.

Sam Vanga
http://SamuelVanga.com

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

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

pietlinden SSC Guru Points: 63198 More actions · Answer 1

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.

Sam Vanga SSCommitted Points: 1742 More actions · Answer 2

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.

Sam Vanga
http://SamuelVanga.com

pietlinden SSC Guru Points: 63198 More actions · Answer 3

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

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

IrfanHyd SSCommitted Points: 1639 More actions · Answer 4

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

---------------------------------------------------------------------------------------------------------------------------------
IrfanHyd

Sam Vanga SSCommitted Points: 1742 More actions · Answer 5

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
http://SamuelVanga.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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)
Intro to Tally Tables and Functions