By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TAB1 (
ID VARCHAR(10),
Start_Date VARCHAR(10),
End_Date VARCHAR(10)
);
INSERT INTO TAB1 VALUES ('001', '2005-01-01', '2006-01-01');
INSERT INTO TAB1 VALUES ('001', '2005-01-01', '2007-01-01');
INSERT INTO TAB1 VALUES ('001', '2008-01-01', '2008-06-01');
INSERT INTO TAB1 VALUES ('001', '2008-04-01', '2008-12-01');
INSERT INTO TAB1 VALUES ('001', '2010-01-01', '2010-05-01');
INSERT INTO TAB1 VALUES ('001', '2010-04-01', '2010-12-01');
INSERT INTO TAB1 VALUES ('001', '2010-11-01', '2012-01-01');
WITH a as (
SELECT
ID,
LEFT(Start_Date, 4) as Year,
MIN(Start_Date) as New_Start_Date
FROM
TAB1
GROUP BY
ID,
LEFT(Start_Date, 4)
), b as (
SELECT
a.ID,
Year,
New_Start_Date,
End_Date
FROM
a
LEFT JOIN
TAB1
ON LEFT(a.New_Start_Date, 4) = LEFT(TAB1.Start_Date, 4)
)
select
ID,
New_Start_Date as Start_Date,
MAX(End_Date)
from
b
GROUP BY
ID,
New_Start_Date;
| ID | Start_Date | MAX(End_Date) |
|---|---|---|
| 001 | 2005-01-01 | 2007-01-01 |
| 001 | 2008-01-01 | 2008-12-01 |
| 001 | 2010-01-01 | 2012-01-01 |