Min and Max Group by Position Segment

Transact-SQL
1

Hello,

I am looking at getting Min (Begin Date of Position) and Max (End Date of Position) information from an Employee Position table. How would I create a query where an employee has transferred in and out of the same position? See picture below:

image
image789×380 9.17 KB

Thanks for any help or suggestions.

2

Welcome,

Please always provide sample data as follows as image is half helpful

declare @seahawks table(employee int, position varchar(50), job_id varchar(50),department int,
effect_Date date, end_date date)

insert into @seahawks
select 100, '9000-50000', 5000,
9000,'2020-08-31', '2021-05-29' union
select 100, '9000-50000', 5000,
9000,'2021-05-30', '2021-06-12' 

select employee, position, job_id, min(effect_Date), max(end_date)
  from @seahawks
  group by employee, position, job_id
3
Current Table
EMPLOYEE POSITION JOB_ID DEPARTMENT EFFECT_DATE END_DATE
100 9000-50000 50000 9000 8/31/2020 5/29/2021
100 9000-50000 50000 9000 5/30/2021 6/12/2021
100 8000-40000 40000 8000 6/13/2021 6/26/2021
100 8000-40000 40000 8000 6/27/2021 7/31/2021
Current position -> 100 9000-50000 50000 9000 8/1/2021
Desired Results
EMPLOYEE POSITION JOB_ID DEPARTMENT EFFECT_DATE END_DATE
100 9000-50000 50000 9000 8/31/2020 6/12/2021
100 8000-40000 40000 8000 6/13/2021 7/31/2021
Current position -> 100 9000-50000 50000 9000 8/1/2021
4

Hi @yosiasz,

Thanks for letting me know on the data. I'm looking for the desired results when an employee moves in and out of the same position.

5

Still not the desired way of providing sample data

6

Did you try what was provided to you?

7

Thanks @yosiasz .

My apologies, what is the best way to provide data? I did try the query, the 5/30/2020 date wouldn't be in the table as it overlaps with the 8/31/2020 - 5/29/2021. When employees move out of the position they transfer to another position and then back into the original position at a later date.

8

Use the sample way I posted to provide your exact data. That one day should be 2021. But basically you need

select employee, position, job_id,
min(effect_Date), max(end_date)
  from yourTables
  group by employee, position, job_id
9

@yosiasz Thanks again. I ran this query with the min and max:

declare @seahawks table(employee int, position varchar(50), job_id varchar(50),department int,
effect_Date date, end_date date)

insert into @seahawks
values
(100, '9000-50000', 5000,9000,'2020-08-31', '2021-05-29'),
(100, '9000-50000', 5000,9000,'2021-05-31', '2021-06-12'),
(100, '8000-40000', 4000,8000,'2021-06-13', '2021-06-26'),
(100, '8000-40000', 4000,8000,'2021-06-27', '2021-07-31'),
(100, '9000-50000', 5000,9000,'2021-08-01', '2050-01-01')

select employee, position, job_id, min(effect_Date) begin_date, max(end_date) end_date
from @seahawks
group by employee, position, job_id

I'm looking to separate out the date from when the employee transferred. So the table results would look like this query:

declare @seahawks1 table(employee int, position varchar(50), job_id varchar(50),department int,
effect_Date date, end_date date)

insert into @seahawks1
values
(100, '9000-50000', 5000,9000,'2020-08-31', '2021-06-12'),
(100, '8000-40000', 4000,8000,'2021-06-13', '2021-07-31'),
(100, '9000-50000', 5000,9000,'2021-08-01', '2050-01-01')

select employee, position, job_id, min(effect_Date) begin_date, max(end_date) end_date
from @seahawks1
group by employee, position, job_id

Any help would be much appreciated.

Thank you.

10

Maybe union with the following

select employee, position, job_id, min(effect_Date) begin_date, max(end_date) end_date
from @seahawks1
Where end_date is not null
group by employee, position, job_id
Union
select employee, position, job_id, effect_Date begin_date, end_date end_date
from @seahawks1
Where end_date is null
11

@yosiasz thanks. I tried that, it doesn't provide the interval of the first begin and end date of the employees position 9000-50000 08-31-2020 thru 06-12-2021.

12

is that interval in your desired result data you show?

13 
WITH Gaps
AS
(
	SELECT employee, position, job_id, department, effect_Date, end_date
			,CASE
				WHEN LAG(end_date) OVER (PARTITION BY employee, position, job_id, department ORDER BY effect_Date)
					= DATEADD(day, -1, effect_Date)
				THEN 0
				ELSE 1
			END AS Gap
	FROM @seahawks
)
,Grps
AS
(
	SELECT employee, position, job_id, department, effect_Date, end_date
		,SUM(Gap) OVER (PARTITION BY employee, position, job_id, department ORDER BY effect_Date) AS Grp
	FROM Gaps
)
SELECT employee, position, job_id, department
	,MIN(effect_Date) AS effect_Date
	,CASE
		WHEN MAX(ISNULL(end_date, '99991231')) = '99991231'
		THEN NULL
		ELSE MAX(end_date)
	END AS end_date
FROM Grps
GROUP BY employee, position, job_id, department, Grp
ORDER BY employee, effect_Date;