Group or Sum the data based on overlapping period

Question

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.

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:

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

Another row for overlapped row

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.

Answer 1

I think I dont have 100% solution, but its hard to decision what data are right and how them sort.

This query is based on lead/lag analytic functions. I had to change NULL values to adequate values in sequence (future and past).

Please try and modify this query and I hope it will fit in your case.

My table:

Query:

SELECT id,name,value,startdate,enddate, 
CASE WHEN nvl(next_startdate,29993112)>nvl(prev_enddate,19900101) THEN 'Y' ELSE 'N' END AS active 
FROM
(
SELECT datatable.*,
lag(enddate) over (partition by id,name order by startdate,value desc) prev_enddate,
lead(startdate) over (partition by id,name order by startdate,value desc) next_startdate
FROM datatable
) dt

Results: