Increment in Rank on encountering particular column value

Question

Status of a Job can progress like below throughout its journey.

ACT > AWI > ISS > EXC > COM > FUR

Sample data is as below.

PIN	JOB_NO	DATE_TIME	JOB_STATUS	SEQ	INSERT_FLAG
XYZ149	C51341353	07/10/2023 06:53:29	ISS	4	0
XYZ149	C51341353	07/10/2023 06:58:29	EXC	5	0
XYZ149	C51341353	07/10/2023 07:23:27	FUR	6	1
XYZ149	C51341353	07/10/2023 07:23:59	ISS	4	0
XYZ149	C51341353	07/10/2023 07:24:17	FUR	6	1
XYZ149	C51341353	07/10/2023 07:31:07	ISS	4	0
XYZ149	C51341353	07/10/2023 07:35:07	EXC	5	0
XYZ149	C51341353	07/10/2023 07:38:07	COM	6	0
XYZ149	C51341353	07/10/2023 07:40:00	FUR	6	1
XYZ149	C51341353	07/10/2023 09:04:56	ISS	4	1

INSERT_FLAG = 1 indicates the latest/last job data in that job progression. calculated based on the change in order of progression in status (Using SEQ)

Whenever the job resets its progression or INSERT_FLAG is 1, increment the RANK.

Note: Finally, this RANK column is used to group the progression to get the DATE_TIME for respective JOB_STATUS in that progression using Max().

Expected output.

PIN	JOB_NO	DATE_TIME	JOB_STATUS	SEQ	INSERT_FLAG	RANK
XYZ149	C51341353	07/10/2023 06:53:29	ISS	4	0	1
XYZ149	C51341353	07/10/2023 06:58:29	EXC	5	0	1
XYZ149	C51341353	07/10/2023 07:23:27	FUR	6	1	1
XYZ149	C51341353	07/10/2023 07:23:59	ISS	4	0	2
XYZ149	C51341353	07/10/2023 07:24:17	FUR	6	1	2
XYZ149	C51341353	07/10/2023 07:31:07	ISS	4	0	3
XYZ149	C51341353	07/10/2023 07:35:07	EXC	5	0	3
XYZ149	C51341353	07/10/2023 07:38:07	COM	6	0	3
XYZ149	C51341353	07/10/2023 07:40:00	FUR	6	1	3
XYZ149	C51341353	07/10/2023 09:04:56	ISS	4	1	4

I tried to use rank() with case statement inside window clause to identify the progression to increment the rank. But not working.