Group cases when group exists partially-CodePudding

How to add the non existing group to a case, which exists within a different date range.

Data:

ID GROUP_ID  DATE_FROM    DATE_TO      DATE_NEW_FROM   DATE_NEW_TO  CASE_ID_GROUP  NEW_ID
1    1       2019-10-05   2019-11-02   2019-10-05      2019-11-02   AAX2           AAX2
1   null     null         null         2019-10-22      2019-10-26   null           AAX3
1   null     null         null         2019-10-24      2019-10-29   null           AAX4
1    2       2018-05-05   2018-05-10   2018-05-05      2018-05-10   CCA2           CCA2
1    3       2020-05-20   2020-06-15   2020-05-20      2020-06-15   BBA1           BBA1
1   null     null         null         2020-05-25      2020-05-29   null           BBA2
2   and so on...

Desired output:

ID GROUP_ID   DATE_NEW_FROM   DATE_NEW_TO  NEW_ID
1    1        2019-10-05      2019-11-02   AAX2
1    1        2019-10-22      2019-10-26   AAX3
1    1        2019-10-24      2019-10-29   AAX4
1    2        2018-05-05      2018-05-10   CCA2
1    3        2020-05-20      2020-06-15   BBA1
1    3        2020-05-25      2020-05-29   BBA2
2 and so on...

When DATE_NEW_FROM BETWEEN DATE_FROM and DATE_TO THEN GROUP_ID, but my date_from and date_to are null. So i dont know how to add it to the desired GROUP_ID.

CodePudding user response：

You can think about this as two steps with a union or as a left self-join. Both involve joining to look for a matching date range:

with notmatched as (select * from T where GROUP_ID is null),
        matched as (select * from T where GROUP_ID is not null)
select
    ID, GROUP_ID, DATE_NEW_FROM, DATE_NEW_TO
from matched
union all
select
    n.ID, m.GROUP_ID, n.DATE_NEW_FROM, n.DATE_NEW_TO
from notmatched n inner join matched m
    on m.ID = n.ID
        and n.DATE_NEW_FROM between m.DATE_FROM and m.DATE_TO
        and n.DATE_NEW_TO   between m.DATE_FROM and m.DATE_TO
order by id, group_id;

Alternately:

select
    n.ID,
    coalesce(m.GROUP_ID, n.GROUP_ID) as GROUP_ID,
    coalesce(m.DATE_NEW_FROM, n.DATE_FROM) as DATE_NEW_FROM,
    coalesce(n.DATE_NEW_TO, n.DATE_TO) as DATE_NEW_TO
from T n /* not matched */ left outer join T m /* matched */
    on m.ID = n.ID and n.GROUP_ID is null
        and n.DATE_NEW_FROM between m.DATE_FROM and m.DATE_TO
        and n.DATE_NEW_TO   between m.DATE_FROM and m.DATE_TO
order by id, group_id;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=373f0d4f9ef5f630c3799000a96bd1ed

CodePudding user response：

You can use JOIN with condition between to create a dataset, in the right side of the table includes real data (with null GROUP_ID) and in the left side of the table include not null GROUP_ID data then check right-side date should exist in left-side then we combine column with coalesce

Demo

select
  t2.ID,
  coalesce(t2.GROUP_ID, t1.GROUP_ID),
  t2.DATE_NEW_FROM,
  t2.DATE_NEW_TO,
  t2.NEW_ID
FROM 
  test t1
  join test t2 on
    t2.DATE_NEW_FROM between t1.DATE_FROM and t1.DATE_TO
 WHERE 
   t1.GROUP_ID notnull