Search code examples
sqloracle-databasegaps-and-islandsdb2-luw

Make a record combining its duplicate records based combination of two columns and assign the Min start_date and Max end_date

This image will show you the problem structure

1

This should be the desired output

2

I want to populate the Min Start_Date and Max End_Date from the duplicate record (duplicate based on Present and Absent column) of that record and make a master record, same with other combination of Present and Absent column.

I have done order by on id and start date, to understand behavior of data. this scenario I have mentioned for only one ID, the other IDs present should also give same kind of output, I can implement the correct logic on whole table if I get a working solution for this example. I have tried using window function yet not achieved any solution. Thanks in advance

Note the number of duplicate records of an ID based on present and absent column are variable.

Solution

  • This is a gaps-and-islands problem. Consider using the difference between row numbers to build groups of "adjacent" records, that you can then merge:

    select id, min(start_date) start_date, max(end_date end_date, present, absent
from (
    select t.*,
        row_number() over(partition by id order by start_date) rn1,
        row_number() over(partition by id, present, absent order by start_date) rn2
    from mytable t
) t
group by id, present, absent, rn1 - rn2
order by 1, 2