SQL & PL/SQL

Hello all,

I need some help figuring out this little challenge.

I have tables of groups and flags and effective and term dates relative to these flags as per the following example :

As you can see, group_A had the same flag combination (N,N) for three successive time periods. I would like to merge all time periods having the same flags into one. In which the effective date will be that of the earliest time period (underlined) and the term date will be the latest (underlined)

So the end result would look like this :

Thanks for your help

Here's the DDL script

drop table TMP_group_test;

create table TMP_group_test (group_name varchar2 (8)

,flag_a varchar2(1)

,flag_b varchar2(1)

,eff_date varchar2(8)

,term_date varchar2(8)

                            );

insert into TMP_group_test values ('Group_A', 'Y', 'Y', '20110101',          '99991231');

insert into TMP_group_test values ('Group_A', 'N', 'N', '20100101',          '20101231');

insert into TMP_group_test values ('Group_A', 'N', 'N', '20090101',          '20091231');

insert into TMP_group_test values ('Group_A', 'N', 'N', '20060101',          '20081231');

insert into TMP_group_test values ('Group_A', 'N', 'Y', '20040101',          '20051231');

insert into TMP_group_test values ('Group_A', 'Y', 'Y', '20030101',          '20031231');

insert into TMP_group_test values ('Group_B', 'N', 'Y', '20040101',          '99991231');

insert into TMP_group_test values ('Group_B', 'N', 'Y', '20030101',          '20031231');

commit;

Message was edited by: user13040446

This is the closest I've been to the solution

I create two ranks ;

Rnk1 : partition by group name, order by eff_date desc : this rank will sort the records from the most recent and reset for each group\

Rnk2: (dense) partition by group name , order by flag_A, flagb : this rank gives each combination of group\flag a number so that they are categorised as “families”

Then I use the analytical min function

Min(eff_date) over (partition by group_name, rnk2) : the idea is that for each member of the same family, the new effective date is the min of the family (and the max for the term date),  at the end I just need a distinct so that the duplicates are gone

Now the problem. As you can see from the query below, record 1 and record 6 (as identified by rownum) are identified as the same family, because they have the same flag combination, but they are not successive so each should keep his own effective date.

If only I can make it distinguish between those two that would solve my problem

Query:

select rownum,group_name, flag_a, flag_b, eff_date, term_date, rnk1, rnk2

,min(eff_date) over (partition by group_name, rnk2) min_eff

from

(

select rownum,

group_name, flag_a, flag_b,eff_date, term_date

,rank() over (partition by group_name order by eff_date desc) rnk1

,dense_rank() over (partition by group_name order by flag_A,flag_B ) rnk2

from dsreports.tmp_group_test

)order by rownum