By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Data (Id VARCHAR(100), First_Date DATE, Second_Date DATE)
INSERT Data
VALUES
('ABC12345', '2022-05-18', '2022-08-16'),
('ABC12345', '2022-05-23', '2022-08-29'),
('ABC12345', '2022-06-06', '2022-11-23'),
('ABC12345', '2022-06-16', '2022-11-23'),
('ABC12345', '2022-08-16', '2022-11-23'),
('ABC12345', '2022-11-01', '2022-11-29'),
('DEF00001', '2022-01-01', '2022-01-02'),
('DEF00001', '2022-01-02', '2022-01-05'),
('DEF00001', '2022-01-03', '2022-01-06'),
('DEF00001', '2022-01-04', '2022-01-07'),
('DEF00001', '2022-01-05', '2022-01-08'),
('DEF00001', '2022-01-10', '2022-01-20'),
('DEF00001', '2022-01-15', '2022-01-25'),
('DEF00001', '2022-01-22', '2022-01-31')
CREATE INDEX IX_Data_Id_FirstDate ON Data(Id, First_Date)
;WITH OrderedData AS (
SELECT
D.*,
ROW_NUMBER() OVER(
PARTITION BY D.Id
ORDER BY D.First_Date, D.Second_Date DESC
) AS RowNum
FROM Data D
),
LinkedData AS (
SELECT D.*, D2.RowNum AS NextRowNum
FROM OrderedData D
OUTER APPLY(
SELECT TOP 1 D2.*
FROM OrderedData D2
WHERE D2.Id = D.Id
| Id | First_Date | Second_Date | RowNum | Flag |
|---|---|---|---|---|
| ABC12345 | 2022-05-18 | 2022-08-16 | 1 | Keep |
| ABC12345 | 2022-05-23 | 2022-08-29 | 2 | Exclude |
| ABC12345 | 2022-06-06 | 2022-11-23 | 3 | Exclude |
| ABC12345 | 2022-06-16 | 2022-11-23 | 4 | Exclude |
| ABC12345 | 2022-08-16 | 2022-11-23 | 5 | Keep |
| ABC12345 | 2022-11-01 | 2022-11-29 | 6 | Exclude |
| DEF00001 | 2022-01-01 | 2022-01-02 | 1 | Keep |
| DEF00001 | 2022-01-02 | 2022-01-05 | 2 | Keep |
| DEF00001 | 2022-01-03 | 2022-01-06 | 3 | Exclude |
| DEF00001 | 2022-01-04 | 2022-01-07 | 4 | Exclude |
| DEF00001 | 2022-01-05 | 2022-01-08 | 5 | Keep |
| DEF00001 | 2022-01-10 | 2022-01-20 | 6 | Keep |
| DEF00001 | 2022-01-15 | 2022-01-25 | 7 | Exclude |
| DEF00001 | 2022-01-22 | 2022-01-31 | 8 | Keep |