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-

Solution 1:

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(casewhen tprev.emp_id isnullthen1else0end) over
                 (partitionby t.emp_id, t.title orderby t.start_date) asgroupingfrom t leftjoin
           t tprev
           on t.emp_id = tprev.emp_id and
              t.title = tprev.title and
              t.start_date = tprev.end_date +1
     ) t
groupbygrouping, emp_id, title;

Solution 2:

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

with cte1 as
(
select a.*,
row_number()over(partitionby emp_id,title orderbystart-date) rn,
row_number() over(orderbystart-date) rn1
from table_name a
) select emp_id,
  min(start-date),
  max(end_date),
  max(title)
  from cte1 groupby emp_id, rn1-rn

demo link