Overlapping intervals

Other SQL Server Topics
1

Hello. I am starter in DB world and I am working with ORACLE DB and have specific problem that I cannot find any sufficient explanaiton on net.

I have table that has columnd (VALID_FROM (date) VALID_UNTIL (date) and COUNT (number))
The VALID FROM is mandatory while VALID_UNTIL is nullable (infinity lets say) and I want to get all intervals (they can overlap and they dont need to overlap) and make them into their repsective non overlapping intervals (and sum all count in nonoverlapping intervals)

This is example of input and output that is correct:
Input:
| VALID FROM | VALID UNTIL | COUNT |
|01/01/2023 | NULL |1 |
|01/10/2023 |19/10/2023 |2 |
|03/10/2023 |12/10/2023 |5 |

Output:
| VALID FROM | VALID UNTIL | COUNT |
|1.1.23 | 30.9.23 |1 |
|1.10.23 | 2.10.23 | 3 |
|3.10.23 | 12.10.23 | 8 |
|13.10.23 | 19.10.23 | 3 |
|20.10.23 | NULL | 1 |

what is the most effieicnet qery to achieve this and how this is by far toughest thing i tried to learn.
periods are nightmere.
Thank you

2

This is a Microsoft SQL Server Forum (T-SQL), I'm not sure it will work with ORACLE. I would create a table with all dates, select the sum of the count for each day and then filter the changes with the function LAG (column trick). Now you have the begin date and the results:

image

DECLARE @Test TABLE
(
	ValidFrom date NOT NULL,
	ValidTill date NULL,
	[COUNT] int NOT NULL
);

DECLARE @start_date DATE = '2023-01-01';
DECLARE @end_date DATE = '2023-12-31';

INSERT INTO @Test
SELECT DATEFROMPARTS(2023,1,1), NULL,1
UNION
SELECT DATEFROMPARTS(2023,10,1), DATEFROMPARTS(2023,10,19),2
UNION
SELECT DATEFROMPARTS(2023,10,3), DATEFROMPARTS(2023,10,12),5;

WITH Calendar AS
(
	SELECT   @start_date AS [Date], 1 AS [level]
               UNION ALL
               SELECT   DATEADD(DAY, 1, [Date]), [level] + 1
               FROM     Calendar
               WHERE    [Date] < @end_date 
			   
), AllDate AS (
SELECT
	C.[Date], SUM(T.[COUNT]) AS [COUNT]
FROM Calendar C
	LEFT JOIN @Test T 
		ON C.[Date] BETWEEN ValidFrom AND ISNULL(ValidTill,DATEFROMPARTS(2023,12,31))
GROUP BY C.[Date]
), AllDateScore AS
(
SELECT *, CASE WHEN LAG([COUNT],1,0) OVER (ORDER BY [Date]) <> [COUNT] THEN 1 ELSE 0 END Trick
FROM AllDate
)
SELECT * 
FROM AllDateScore A
WHERE Trick=1
OPTION (MAXRECURSION 0);