CODEWITHSUNDEEP
sqlsql-server
Itsallgonepearshaped
Itsallgonepearshaped

Reputation: 190

Problems with Min/Max Group by in SQL

I'm trying to do a sql query that will consolidate a list of employee contracts. It works successfully, apart from certain employees where they've changed jobs back and forth multiple times.

the data:

EmpNo   From Date   To Date Job Title
241780  2013-11-11  2013-11-24  Sales Associate
241780  2013-11-25  2014-01-04  Sales Associate
241780  2014-01-05  2014-02-28  Sales Associate - Accessories
241780  2014-03-01  2014-03-19  Sales Associate
241780  2014-03-20  2015-02-09  Sales Associate
241780  2015-02-10  2015-02-28  Sales Associate - Accessories
241780  2015-03-01  2016-10-16  Sales Associate - Accessories
241780  2016-10-17  2016-10-23  Customer Service Advisor
241780  2016-10-24  2016-11-13  Customer Service Advisor
241780  2016-11-14  2017-03-31  Customer Service Advisor
241780  2017-04-01  2030-01-01  Customer Service Advisor

The problem comes from my code:

Select [Employee No], 
MIN([Effective From Date]),
MAX([Effective To Date]),
[Job Title]
From [dbo].[MYTABLE]

Group by 
[Employee No],
[Job Title]
order by [Employee No],MIN([Effective From Date])

Gives me overlapping dates

EmpNo   Start       TO          Job Title
241780  2013-11-11  2015-02-09  Sales Associate
241780  2014-01-05  2016-10-16  Sales Associate - Accessories
241780  2016-10-17  2030-01-01  Customer Service Advisor

I need the following:

241780  2013-11-11  2014-01-04  Sales Associate
241780  2014-01-05  2012-02-28  Sales Associate - Accessories
241780  2014-03-01  2015-02-09  Sales Associate
241780  2015-02-10  2016-10-16  Sales Associate - Accessories
241780  2016-10-17  2030-01-01  Customer Service Advisor

I've tried doing a Partition by with no success, but not sure it will give me the result I'm after.

Any ideas?

Upvotes: 2

Views: 265

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

This is an example of a "gap-and-islands" problem. One method uses the difference of row numbers to define the groups:

select empno, jobtitle, min(start) as start, max(to) as to
from (select t.*,
             row_number() over (partition by empno order by start) as seqnum_e,
             row_number() over (partition by empno, jobtitle order by start) as seqnum_ej,
      from t
     ) t
group by empno, jobtitle, (seqnum_e - seqnum_ej);

Upvotes: 1

Related Questions