How to get min and max date in SQL since branch the branch code changed

I have table as below. I'm using plsql

ID	BranchCode	StartDate	FinishDate
1111	89	07.06.2012	12.08.2013
1111	89	13.08.2013	09.03.2015
1111	1167	10.03.2015	30.09.2015
1111	548	01.10.2015	08.10.2015
1111	548	09.12.2015	31.07.2016
1111	548	01.08.2016	24.08.2017
1111	89	25.08.2017	19.03.2018
1111	89	20.03.2018	30.06.2019
1111	89	01.07.2019	today

And I want to find min and max dates each branch changes. I partioned based on branch_code and emp_ID but I couldn't what I want.

And this is what I want;

ID	BranchCode	MinStartDate	MaxFinishDate
1111	89	07.06.2012	09.03.2015
1111	1167	10.03.2015	30.09.2015
1111	548	01.10.2015	24.08.2017
1111	89	25.08.2015	today

I want to find min and max date when branch changed. And also calculate time between min and max date.

Solution

This is a gaps and islands problem, and we can use the difference in row numbers method here.

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID
                                   ORDER BY StartDate) rn1,
                ROW_NUMBER() OVER (PARTITION BY ID, BranchCode
                                   ORDER BY StartDate) rn2
    FROM yourTable t
)

SELECT
    ID,
    BranchCode,
    MIN(StartDate) AS MinStartDate,
    MAX(FinishDate) AS MaxFinishDate
FROM cte
GROUP BY
    ID,
    BranchCode,
    rn1 - rn2
ORDER BY
    ID,
    MIN(StartDate);

The basic idea here is to form a pseudo group column defined by the ID, BranchCode, and a difference in row numbers sequence. Then, all we need to is aggregate and take the appropriate min/max.