CODEWITHSUNDEEP
sqloracle-database
Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

Split records based on Startdate and enddate

Input set,

CMP BND_CD STARTDATE  ENDDATE
01  UF     03/15/2010 07/01/2010
01  TRL    03/15/2010 12/15/2014

Dummy Data

CREATE TABLE DATA ( CMP, BND_CD, STARTDATE, ENDDATE ) AS
  SELECT '01','UF',  DATE '2010-03-15', DATE '2010-07-01' FROM DUAL UNION ALL
  SELECT '01','TRL', DATE '2010-03-15', DATE '2010-12-15' FROM DUAL;

I need the output as 3 records, which split the TRL/UF records, ie,

CMP BND_CD STARTDATE  ENDDATE
01  UF     03/15/2010 07/01/2010
01  TRL    03/15/2010 07/01/2010
01  TRL    07/01/2010 12/15/2014

The split should be done for each CMP code for the two BND_CD, if the date ranges overlaps each other.We have to group the records based on CMP value, then If TRL overlaps UF, TRL reords should be splitted,or if UFoverlaps TRL, UF records should be splitted.

Upvotes: 0

Views: 87

Answers (1)

MT0
MT0

Reputation: 167972

Query:

SELECT u.cmp,          -- UF values preceding the TRL range
       u.bnd_cd,
       u.start_date,
       LEAST( u.end_date, t.start_date )
FROM   data t
       INNER JOIN
       data u
       ON (    t.bnd_cd = 'TRL'
           AND u.bnd_cd = 'UF'
           AND t.cmp = u.cmp
           AND u.start_date < t.start_date )
UNION ALL
SELECT u.cmp,          -- UF values within the TRL range
       u.bnd_cd,
       GREATEST( u.start_date, t.start_date ),
       LEAST( u.end_date, t.end_date )
FROM   data t
       INNER JOIN
       data u
       ON (    t.bnd_cd = 'TRL'
           AND u.bnd_cd = 'UF'
           AND t.cmp = u.cmp
           AND u.start_date <= t.end_date
           AND u.end_date   >= t.start_date )
UNION ALL
SELECT u.cmp,          -- UF values following the TRL range
       u.bnd_cd,
       GREATEST( u.start_date, t.end_date ),
       u.end_date
FROM   data t
       INNER JOIN
       data u
       ON (    t.bnd_cd = 'TRL'
           AND u.bnd_cd = 'UF'
           AND t.cmp = u.cmp
           AND u.end_date > t.end_date )
UNION ALL
SELECT t.cmp,          -- TRL values preceding the UF range
       t.bnd_cd,
       t.start_date,
       LEAST( t.end_date, u.start_date )
FROM   data t
       INNER JOIN
       data u
       ON (    t.bnd_cd = 'TRL'
           AND u.bnd_cd = 'UF'
           AND t.cmp = u.cmp
           AND t.start_date < u.start_date )
UNION ALL
SELECT t.cmp,          -- TRL values within the UF range
       t.bnd_cd,
       GREATEST( t.start_date, u.start_date ),
       LEAST( t.end_date, u.end_date )
FROM   data t
       INNER JOIN
       data u
       ON (    t.bnd_cd = 'TRL'
           AND u.bnd_cd = 'UF'
           AND t.cmp = u.cmp
           AND t.start_date <= u.end_date
           AND t.end_date   >= u.start_date )
UNION ALL
SELECT t.cmp,          -- TRL values following the UF range
       t.bnd_cd,
       GREATEST( t.start_date, u.end_date ),
       t.end_date
FROM   data t
       INNER JOIN
       data u
       ON (    t.bnd_cd = 'TRL'
           AND u.bnd_cd = 'UF'
           AND t.cmp = u.cmp
           AND t.end_date > u.end_date )

Output:

CMP BND_CD START_DATE END_DATE
--- ------ ---------- ----------
01  UF     2010-03-15 2010-07-01
01  TRL    2010-03-15 2010-07-01
01  TRL    2010-07-01 2010-08-01

Upvotes: 1

Related Questions