CODEWITHSUNDEEP
sqloracle-database
Adeel Aslam
Adeel Aslam

Reputation: 1294

oracle sql need help on data according to pattern

I have sample data in oracle database shown below :

set_no  set_eff_dt    set_term_dt
1000    1/1/2015    12/31/2016
1000    1/1/2017    10/31/2017
1000    11/1/2017   12/31/2018
1000    2/1/2019    10/31/2019
1000    11/1/2019   10/31/2020

I want the Out Like below

1000    1/1/2015    12/31/2018
1000    2/1/2019    10/31/2020

Let me Explain the Pattern and how the out put will Come

In the second row set_off_dt is result of set_term_dt +1

In the third row set_off_dt is result of set_term_dt +1 of second row

In the 4th row set_eff_dt is not a result of set_term_dt+1 from third row so here is group break may b

In the 5th row set_eff_dt is again a result of set_term_dt+1 from 4th row so it will be collapsed with 4th row as shown in output

In this same pattern we have thousand of records and we want to collapsed as per logic described

what i have tried

SELECT SET_NO,SET_EFF_DT,
 case when LEAD (SET_EFF_DT,1) OVER (ORDER BY SET_EFF_DT)-1 = set_trm_dt then 1 else 0 end flg
 FROM xx_fl_test

I was just able to identify the flag if SET_EFF_DT = set_trm_dt in new row... But still i dont got understand how tackle the collapsed on behalf of this data.

Upvotes: 1

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

This is a gaps-and-islands problem. I would solve by calculating a grouping variable, in the following steps:

  1. Determine where a group begins. For this, I do a lag on the previous set_trm_dt and case logic to see if there is no "connection".
  2. Do a cumulative sum of the the flag, to assign a grp to each row.
  3. Aggregate by grp.

The code looks like this:

select set_no, min(set_eff_dt), max(set_trm_dt)
from (select t.*,
             sum(case when set_eff_dt > prev_set_trm_dt + 1 then 1 else 0 end) over (partition by set_no order by set_eff_dt) as grp
      from (select t.*,
                   lag(set_trm_dt) over (partition by set_no order by set_eff_dt) as prev_set_trm_dt
            from xx_fl_test t
           ) t
     ) t
group by set_no, grp;

Upvotes: 2

Parfait
Parfait

Reputation: 107587

Consider cumulatively summing your generated column to produce a grouping variable which requires two CTEs: one for your flg calculation, and second for cumulative sum of flg with a window function. Finally, aggregate by cum_flg (but conditionally add 1 for the very first grouping value which starts as 1).

WITH sub AS
  (SELECT SET_NO, SET_EFF_DT, SET_TRM_DT,
          CASE WHEN LEAD (SET_EFF_DT,1) OVER (ORDER BY SET_EFF_DT)-1 = SET_TRM_DT
               THEN 1 
               ELSE 0 
          END AS flg
   FROM xx_fl_test),

   calc AS
   (SELECT SET_NO, SET_EFF_DT, SET_TRM_DT,
           SUM (flg) OVER (PARTITION BY SET_NO ORDER BY SET_EFF_DT)  AS cum_flg
    FROM sub)

SELECT SET_NO, 
       MIN(SET_EFF_DT) AS MIN_SET_EFF_DT,
       MAX(SET_TRM_DT) AS MAX_SET_TRM_DT
FROM calc
GROUP BY SET_NO,
         CASE cum_flg
              WHEN 1
              THEN cum_flg + 1
         END

Rextester Demo

Upvotes: 1

Related Questions