How to display date ranges ignoring older entries covered by newer entries in SQL Server

We need to filter out some date ranges that intersect with newer rows how might we do this?

This is the SQL I have at the moment, what we need if for it to just return then last 3 rows as the first 2 rows are covered by later entries that were added.

DROP TABLE IF EXISTS #ranges

CREATE TABLE #ranges (
	StartDate DATETIME2(7), 
	EndDate DATETIME2(7),
	CreatedDate DATETIME2(7),
	Cost DECIMAL
);

INSERT INTO #ranges(StartDate, EndDate, CreatedDate, Cost)
VALUES
('2024-01-02 00:00:00', '2024-01-05 00:00:00', '2023-07-10 19:40:19.60', 10.00),
('2024-01-02 00:00:00', '2024-01-03 00:00:00', '2023-07-10 19:41:45.73', 5.00),
('2024-01-02 00:00:00', '2024-01-05 00:00:00', '2023-07-10 19:45:32.76', 22.00),
('2024-01-03 00:00:00', '2024-01-05 00:00:00', '2023-07-10 19:54:41.60', 44.00),
('2024-01-04 00:00:00', '2024-01-04 00:00:00', '2023-07-10 19:54:52.60', 50.00);

We would like to get the data like this, the reason being that the last 3 rows cover that date period in this case the 1st to the 5th of Jan. There may be an overlap but there cover that range. Or should we do something else e.g. just return a row for each day with the cost for that day?

It will look like this in a table it is output in

Many thanks

You can use the following query

DELETE r1
FROM ranges r1
WHERE EXISTS (SELECT 1
    FROM (
        SELECT *,
          PrevEndDate = LAG(EndDate) OVER (ORDER BY StartDate)
        FROM ranges r2
        WHERE r2.CreatedDate > r1.CreatedDate
          AND r1.StartDate < r2.EndDate
          AND r1.EndDate > r2.StartDate
    ) r2
    HAVING COUNT(CASE WHEN PrevEndDate < StartDate THEN 1 END) = 0
       AND MIN(r2.StartDate) <= r1.StartDate
       AND MAX(r2.EndDate)   >= r1.EndDate
);

db<>fiddle

The logic is as follows:

• Take all rows in ranges

• For each one, ensure that there exists a set of other rows such that:

  • All rows are CreatedDate after this one.
  • All rows overlap this one (by doing a standard start/end comparison to get an overlap check)
  • Sort the inner set by StartDate and get the previous EndDate for each.
  • Ensure that the set as a whole does not contain gaps.
  • And that the lowest StartDate is less than or equal to the outer row.
  • And that the highest EndDate is greater than or equal to the outer row.

There may be a more elegant solution with fewer iterations over the data, however, here is a straightforward approach, for your first question.

SELECT * FROM
(
	SELECT *,
		RANK() OVER(PARTITION BY GroupId ORDER BY CreatedDate DESC) As GroupRank
	FROM
	(
		SELECT *,
			SUM(StartDateBoundries) OVER (ORDER BY CreatedDate ROWS UNBOUNDED PRECEDING) GroupId
		FROM
		(
			SELECT *,		
				CASE WHEN LAG(StartDate) OVER (ORDER BY StartDate) <> StartDate OR LAG(StartDate) OVER (ORDER BY StartDate) IS NULL THEN 1 ELSE 0 END AS StartDateBoundries
			FROM #ranges
		) AS X 
	)AS Y
)AS Z
WHERE
	GroupRank = 1