SQLTeam.com | Weblogs | Forums

Min and Max Group by Position Segment

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:

Thanks for any help or suggestions.

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
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

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.

Still not the desired way of providing sample data

Did you try what was provided to you?

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.

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

@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.

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

@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.

is that interval in your desired result data you show?

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;