Aggregate date ranges without including overlapping dates

Solution 1:

You can use:

WITH days ( asset_id, loan_start, day_end, loan_end) AS (
  SELECT asset_id,
         loan_start,
         LEAST(TRUNC(loan_start) + INTERVAL '1' DAY, loan_end),
         loan_end
  FROM   table_name
  MATCH_RECOGNIZE (
    PARTITION BY asset_id
    ORDER     BY loan_start, loan_end
    MEASURES
     FIRST(loan_start) AS loan_start,
     MAX(loan_end)     AS loan_end
   ONE ROW PER MATCH
   PATTERN (overlaps* next_time)
   DEFINE
     overlaps  AS MAX(overlaps.loan_end) >= loan_start,
     next_time AS MAX(overlaps.loan_end) >= loan_start
               OR MAX(overlaps.loan_end) IS NULL
   )
UNION ALL
  SELECT asset_id,
         day_end,
         LEAST(TRUNC(day_end) + INTERVAL '1' DAY, loan_end),
         loan_end
  FROM   days
  WHERE  day_end < loan_end
)
SEARCH DEPTH FIRST BY asset_id, loan_start SET order_id
SELECT asset_id,
       TO_CHAR(TRUNC(loan_start), 'YYYY-MM-DD') AS day,
       SUM(day_end - loan_start) AS amt_unavailable
FROM   days
GROUP BY
       asset_id,
       TRUNC(loan_start)

Which, for the sample data:

CREATE TABLE table_name (ASSET_ID, LOAN_START, LOAN_END) AS
SELECT '00001', DATE '2022-01-01' + INTERVAL '14:00' HOUR TO MINUTE, DATE '2022-01-03' + INTERVAL '08:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '00002', DATE '2022-01-15' + INTERVAL '08:30' HOUR TO MINUTE, DATE '2022-01-15' + INTERVAL '10:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '00002', DATE '2022-01-15' + INTERVAL '09:30' HOUR TO MINUTE, DATE '2022-01-15' + INTERVAL '10:30' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '00002', DATE '2022-01-15' + INTERVAL '12:00' HOUR TO MINUTE, DATE '2022-01-15' + INTERVAL '18:00' HOUR TO MINUTE FROM DUAL;

Outputs:

ASSET_ID	DAY	AMT_UNAVAILABLE
00001	2022-01-01	.4166666666666666666666666666666666666667
00001	2022-01-02	1
00001	2022-01-03	.3333333333333333333333333333333333333333
00002	2022-01-15	.3333333333333333333333333333333333333333

db<>fiddle here