[SQL] Min date in consecutive group before breakage

There is 1 table wherein all these records are maintained for 1 employee. Due to one more column in this table which is not shown in this example the person has multiple rows for even 1 job , i.e. 2 rows for job J1 , 3 for Job J2 and again 2 rows for job J1 in order. The person switches from job J1 to J2 and then again J1.How to extract the required rows whenever a switch happens as I have to extract the min of that row when job is changed starting backwards from Dec month as the checking needs to be done within Jan to Dec.

So for J1 output should be 1 Sep 2021 is what I need as before that there is breakage in J1 job

StDt EdDt Job Required_Rows
1-Jan-21 31-Jan-21 J1 N
1-Feb-21 30-Jun-21 J1 Y
1-Jul-21 30-Jul-21 J2 N
1-Aug-21 15-Aug-21 J2 N
16-Aug-21 31-Aug-21 J2 Y
1-Sep-21 1-Nov-21 J1 N
2-Nov-21 31-Dec-21 J1

Continue reading...