CODEWITHSUNDEEP
sqloracle-databaseoracle12c
StanGeo
StanGeo

Reputation: 429

How to create GROUP BY on min and max date

I have a database table like this

emp_id start-date  end_date    title    location
111    1-JAN-2000  31-DEC-2003 MANAGER  NYO
111    1-JAN-2003  31-DEC-2005 MANAGER  BOM
111    1-JAN-2006  31-DEC-2007 CFO      NYO
111    1-JAN-2008  31-DEC-2015 MANAGER  NYO

I have created a SQL code already with GROUP BY and min , max function

select emp_id,min(start_date),max(end_date),title
from table1
group by emp_id,title

What is expect is this:

111 1-JAN-2000 31-DEC-2005 MANAGER
111 1-JAN-2006 31-DEC-2007 CFO
111 1-JAN-2008 31-DEC-2015 MANAGER

What i am getting is:

111 1-JAN-2000 31-DEC-2015 MANAGER 
111 1-JAN-2006 31-DEC-2007 CFO

Upvotes: 1

Views: 1497

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This is a type of gaps-and-islands problem with date-chains. I would suggest using a left join to find where the islands start. Then a cumulative sum and aggregation:

select emp_id, title, min(start_date), max(end_date)
from (select t.*,
             sum(case when tprev.emp_id is null then 1 else 0 end) over
                 (partition by t.emp_id, t.title order by t.start_date) as grouping
      from t left join
           t tprev
           on t.emp_id = tprev.emp_id and
              t.title = tprev.title and
              t.start_date = tprev.end_date + 1
     ) t
group by grouping, emp_id, title;

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below by using window function find the gap and make it the group 

with cte1 as
(
select a.*,
row_number()over(partition by emp_id,title order by start-date) rn,
row_number() over(order by start-date) rn1
from table_name a
) select emp_id,
  min(start-date),
  max(end_date),
  max(title)
  from cte1 group by emp_id, rn1-rn

demo link

Upvotes: 0

Related Questions