December 9, 2021 at 7:26 pm
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
December 9, 2021 at 7:29 pm
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.
December 9, 2021 at 7:43 pm
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
December 10, 2021 at 1:30 am
Totally missed it... sounds like the classic Islands problem. (Clump all contiguous spans together).
SQL Server Window Functions Gaps and Islands Problem (mssqltips.com)
December 10, 2021 at 1:38 am
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
=======================================================================
December 10, 2021 at 8:41 am
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
December 10, 2021 at 5:14 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply