Separating Overlapping Date Ranges in Oracle

Question

I have data with overlapping data ranges. Example below

Customer_ID	FAC_NUM	Start_Date	End_Date	New_Monies
12345	ABC1234	26/NOV/2014	26/MAY/2015	100000
12345	ABC1234	12/DEC/2014	12/JUN/2015	200000
12345	ABC1234	15/JUN/2015	15/DEC/2015	500000
12345	ABC1234	20/DEC/2015	20/JUN/2016	600000

question from:https://stackoverflow.com/questions/65557342/separating-overlapping-date-ranges-in-oracle

Answer 1

with
  prep (customer_id, fac_num, dt, amount) as (
    select t.customer_id, t.fac_num,
           case h.col when 's' then t.start_date else t.end_date + 1 end as dt,
           case h.col when 's' then t.new_monies else - t.new_monies end as amount
    from   sample_data t 
           cross join
           (select 's' as col from dual union all select 'e' from dual) h
  )
, cumul_sums (customer_id, fac_num, dt, amount) as (
    select distinct
           customer_id, fac_num, dt, 
           sum(amount) over (partition by customer_id, fac_num order by dt)
    from   prep
  )
, with_intervals (customer_id, fac_num, start_date, end_date, amount) as (
    select customer_id, fac_num, dt, 
           lead(dt) over (partition by customer_id, fac_num order by dt) - 1,
           amount
    from   cumul_sums
  )
select customer_id, fac_num, start_date, end_date, amount
from   with_intervals
where  end_date is not null
order  by customer_id, fac_num, start_date
;

The prep subquery unpivots the inputs, while at the same time changing the "end date" to the "start date" of the following interval and assigning a positive amount to the "start date" and the negative of the same amount to the following "start date". cumul_sums computes the cumulative sums; note that if two or more intervals begin on the same date (so the same date from prep appears multiple times for a customer and fac_num), the analytic sum will include the amounts from ALL the rows up to that date - the default windowing clause is range between...... After the cumulative sums are computed, this subquery also de-duplicates the output rows (to handle precisely that complication, of multiple intervals starting on the same date). with_intervals recovers the "start date" - "end date" intervals, and the final step simply removes the last interval ("to infinity") which would have an "amount" of zero.

EDIT This solution answers the OP's original question. After posting the solution, the OP changed the question. The solution can be changed easily to address the new formulation. I'm not going to chase shadows though; the solution will remain as is.