Merging adjacent date intervals into a single row

gaps-and-islandsoracle

Here is the scenario:
If the employee transaction is continuous display as-is. if the employee got any gap display as a new row.

Below is the sample table

Emid    Fromdate    Todate
-----   --------    ------
A   1/1/2012    12/31/2012
A   12/31/2012  12/31/2013
A   12/31/2013  12/31/2014
A   2/2/2015    12/31/2099

Expected output:

emid    Fromdate    Todate
======  ========    ========
A   1/1/2012    12/31/2014
A   2/2/2015    12/31/2099

Best Answer

You could try this:

[edit] fixed a bug .. comment on line fixed [/edit]

with w_data as (
        select 'A' emid, to_date('01/01/2012','mm/dd/yyyy') fromdate, to_date('12/31/2012','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2012','mm/dd/yyyy') fromdate, to_date('12/31/2013','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2013','mm/dd/yyyy') fromdate, to_date('12/31/2014','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('02/02/2015','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('12/30/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('03/22/2014','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('01/10/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('04/03/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual  
        ),
  w_sub as (
     select emid, fromdate, todate,
              sum(grp) over (partition by emid order by fromdate) grp_id  -- added partition by .. forgot to group it by employee
       from (
              select emid, fromdate, todate,
                     case when fromdate = lag(todate) over (partition by emid order by fromdate) 
                       then 0 else 1 end  grp
                from w_data
             )
        )
select emid, min(fromdate) fromdate, max(todate) todate
  from w_sub
 group by emid, grp_id
 order by emid, grp_id, fromdate
/

E FROMDATE    TODATE
- ----------- -----------
A 01-jan-2012 31-dec-2014
A 02-feb-2015 31-dec-2099
B 12-may-2013 30-dec-2013
B 10-jan-2014 22-mar-2014
B 03-apr-2014 31-dec-2099
C 12-may-2013 31-dec-2099

6 rows selected.

Related Solutions

How to retrieve the loop record in the connect by clause

You could use what your query returns to get the extra row that have CONNECT_BY_ISCYCLE=1

WITH a AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE 
   CONNECT BY NOCYCLE
    PRIOR MANAGER_EMP_ID = EMP_ID 
    START WITH EMP_ID = '12345'
)
SELECT EMP_ID, FIRST_NAME
FROM a

UNION ALL

SELECT m.EMP_ID, m.FIRST_NAME
FROM MYTABLE m
  JOIN a
    ON a.MANAGER_EMP_ID = m.EMP_ID
WHERE a.CONNECT_BY_ISCYCLE = 1 ;

Or use a recursive CTE:

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE 
WHERE EMP_ID = '12345'

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m 
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
    AND a.MANAGER_EMP_ID <> a.EMP_ID   -- to avoid the cycles
)
SELECT EMP_ID, FIRST_NAME
FROM a ;

Another way to avoid cycles in a recursive CTE is to use the CYCLE clause (available only in Oracle):

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE 
WHERE EMP_ID = 1

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m 
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
)
CYCLE MANAGER_EMP_ID 
  SET IS_CYCLE TO 'Y' DEFAULT 'N'
SELECT EMP_ID, FIRST_NAME, IS_CYCLE
FROM a ;

Test at SQLfiddle

Just realized that the extra row can be retrieved with a simple change in the original query:

SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE 
   CONNECT BY NOCYCLE
     PRIOR MANAGER_EMP_ID = EMP_ID 
     AND PRIOR MANAGER_EMP_ID <> PRIOR EMP_ID 
                                 -- if she's a manager
                                 -- don't try to connect her with superiors
   START WITH EMP_ID = '12345' ;

How to group joined tables by aggregate function, specifically by average group-name type

ORA-00979 not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Gt.description column is not listed in the group by clause. Columns not participating in group function should be listed in group by clause.

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2) AS "Average Grade"
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code,
         gt.description
ORDER BY gt.grade_type_code;

Best Answer

Related Solutions

How to retrieve the loop record in the connect by clause

How to group joined tables by aggregate function, specifically by average group-name type

ORA-00979 not a GROUP BY expression

Related Question