CODEWITHSUNDEEP
sqloracle-database
LetEpsilonBeLessThanZero
LetEpsilonBeLessThanZero

Reputation: 2403

Truncating multiple date periods

Here is an image describing the table I'm working with (TBL_CHILDREN) as well as the desired output I'm trying to achieve.

enter image description here

The desired table wants to have a separate row for each new combination of active CHILD_ID's under the same PARENT_ID. So, for example, from 2017-01-01 to 2017-02-28 only CHILD_ID 1 was active so the desired table has a row spanning 2017-01-01 to 2017-02-28. But then on 2017-03-01 CHILD_ID 2 also became effective, so I need a new row to reflect the period where CHILD_ID 1 and 2 were active at the same time. And so on and so forth until I have a row describing each period of CHILD_ID combinations.

Here is some code for TBL_CHILDREN:

WITH TBL_CHILDREN AS (SELECT 57 PARENT_ID, 1 CHILD_ID, TO_DATE('2017-01-01','YYYY-MM-DD') START_DATE, TO_DATE('9999-12-31','YYYY-MM-DD') END_DATE FROM dual UNION ALL
                      SELECT 57 PARENT_ID, 2 CHILD_ID, TO_DATE('2017-03-01','YYYY-MM-DD') START_DATE, TO_DATE('2017-05-31','YYYY-MM-DD') END_DATE FROM dual UNION ALL
                      SELECT 57 PARENT_ID, 3 CHILD_ID, TO_DATE('2017-04-01','YYYY-MM-DD') START_DATE, TO_DATE('2017-10-31','YYYY-MM-DD') END_DATE FROM dual)
SELECT *
FROM TBL_CHILDREN

Upvotes: 2

Views: 92

Answers (2)

MT0
MT0

Reputation: 168232

Using UNPIVOT with LAG or LEAD analytic function will do it in a single table scan:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table TBL_CHILDREN ( parent_id, child_id, start_date, end_date )AS
SELECT 57, 1, DATE '2017-01-01', DATE '9999-12-31' FROM dual UNION ALL
SELECT 57, 2, DATE '2017-03-01', DATE '2017-05-31' FROM dual UNION ALL
SELECT 57, 3, DATE '2017-04-01', DATE '2017-10-31' FROM dual;

Query 1:

SELECT *
FROM   (
  SELECT PARENT_ID,
         DT AS start_date,
         LEAD( DT ) OVER ( PARTITION BY parent_id ORDER BY DT ) AS end_date
  FROM   TBL_CHILDREN
  UNPIVOT( dt FOR start_end IN ( start_date, end_date ) )
)
WHERE  end_date IS NOT NULL

Results:

| PARENT_ID |           START_DATE |             END_DATE |
|-----------|----------------------|----------------------|
|        57 | 2017-01-01T00:00:00Z | 2017-03-01T00:00:00Z |
|        57 | 2017-03-01T00:00:00Z | 2017-04-01T00:00:00Z |
|        57 | 2017-04-01T00:00:00Z | 2017-05-31T00:00:00Z |
|        57 | 2017-05-31T00:00:00Z | 2017-10-31T00:00:00Z |
|        57 | 2017-10-31T00:00:00Z | 9999-12-31T00:00:00Z |

Query 2 and this will get the parent and child ids for each time period:

SELECT *
FROM   (
  SELECT parent_id,
         ( SELECT LISTAGG( child_id, ',' ) WITHIN GROUP ( ORDER BY child_id )
           FROM   TBL_CHILDREN c
           WHERE  u.dt >= c.START_DATE
           AND    u.dt <  c.END_DATE ) AS child_ids,
         DT AS start_date,
         LEAD( DT ) OVER ( PARTITION BY parent_id ORDER BY DT ) AS end_date
  FROM   TBL_CHILDREN
  UNPIVOT( dt FOR start_end IN ( start_date, end_date ) ) u
)
WHERE  end_date IS NOT NULL

Results:

| PARENT_ID | CHILD_IDS |           START_DATE |             END_DATE |
|-----------|-----------|----------------------|----------------------|
|        57 |         1 | 2017-01-01T00:00:00Z | 2017-03-01T00:00:00Z |
|        57 |       1,2 | 2017-03-01T00:00:00Z | 2017-04-01T00:00:00Z |
|        57 |     1,2,3 | 2017-04-01T00:00:00Z | 2017-05-31T00:00:00Z |
|        57 |       1,3 | 2017-05-31T00:00:00Z | 2017-10-31T00:00:00Z |
|        57 |         1 | 2017-10-31T00:00:00Z | 9999-12-31T00:00:00Z |

Upvotes: 2

krokodilko
krokodilko

Reputation: 36107

Please take a look at this demo

WITH qqq AS (
  SELECT * FROM TBL_CHILDREN
  START WITH child_id = 1
  CONNECT BY PRIOR parent_id = parent_id AND PRIOR child_id + 1 = child_id
)
SELECT * FROM (
  SELECT PARENT_ID, 
         d as start_date,
         lead(d) over (partition by PARENT_ID order by d ) - 1 as end_date
  FROM (
    SELECT PARENT_ID, start_date as d FROM qqq
    UNION
    SELECT PARENT_ID, end_date FROM qqq
  )
)
WHERE end_date is not null
ORDER by PARENT_ID, start_date
;

| PARENT_ID |           START_DATE |             END_DATE |
|-----------|----------------------|----------------------|
|        57 | 2017-01-01T00:00:00Z | 2017-02-28T00:00:00Z |
|        57 | 2017-03-01T00:00:00Z | 2017-03-31T00:00:00Z |
|        57 | 2017-04-01T00:00:00Z | 2017-05-30T00:00:00Z |
|        57 | 2017-05-31T00:00:00Z | 2017-10-30T00:00:00Z |
|        57 | 2017-10-31T00:00:00Z | 9999-12-30T00:00:00Z |

Upvotes: 1

Related Questions