Group the data based falls between the same period

I'm working on migrating legacy system data to a new system. I'm trying to migrate the data with history based on changed date. My current query results to below output .

enter image description here

Since it's a legacy system, some of the data falls within same period. I want to group the data based on id and name, and add the value as active record or inactive based on the data falls under same period.

My expected output:

enter image description here

For example, lets take 119 as an example and explain the same. One row marked as yellow since its not falls any overlapping period between other rows, but other two rows overlaps the period 01-No-18 to 30-Sep-19.

I need to split the data for overlapping period, and add the value only for overlapped period. So I need to look for combination based on date, which results to introduce a two rows one for non overlapped which results to below two rows

enter image description here

Another row for overlapped row

enter image description here

Same scenario applied for 148324, two rows introduced, one for overlapped and another non overlapped row.

Also is it possible to get non-overlapped data alone based on any condition? I want to move overlapping data alone to temp table, and I can move the non-overlapped data directly to output table.



Read more here: https://stackoverflow.com/questions/65722645/group-the-data-based-falls-between-the-same-period

Content Attribution

This content was originally published by Abdul at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: