SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

set dateformat mdy;
create table hrcmn_emp_full_table
(
employee_code           varchar(Max),
movement_code           varchar(Max),
effective_date          varchar(10),
filename                varchar(Max),
file_sequence           int identity(1,1),
process_sequence        int,
effective_date_sequence int,
final_process_sequence  int,
MyGroup int
)

insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('1001','NEWHR','2020-02-01','XXX.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('1001','NEWHR','2020-01-01','XXX.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('1001','NEWHR','2020-01-01','XXX.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('1001','NEWHR','2020-02-01','XXX.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('1001','NEWHR','2020-01-01','XXX.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('2001','NEWHR','2020-02-01','YYY.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('2001','NEWHR','2020-01-01','YYY.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('2001','NEWHR','2020-01-01','YYY.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('2001','NEWHR','2020-02-01','YYY.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('2002','NEWHR','2020-02-01','YYY.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('2001','NEWHR','2020-01-01','YYY.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('3001','NEWHR','2020-01-01','ZZZ.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('3001','NEWHR','2020-02-01','ZZZ.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('3002','NEWHR','2020-02-01','ZZZ.txt')
insert into hrcmn_emp_full_table(employee_code,movement_code,effective_date,filename) values ('3001','NEWHR','2020-02-01','ZZZ.txt')
15 rows affected

with dataX as (
    select *,
        lag(effective_date) over (
            partition by filename
            order by file_sequence) as last_effective_date
    from hrcmn_emp_full_table
)
select employee_code, effective_date, filename, file_sequence,
    sum(case when effective_date = last_effective_date then 0 else 1 end) over (
        partition by filename
        order by file_sequence) as final_process_sequence
from dataX
order by file_sequence;


employee_code
effective_date
filename
file_sequence
final_process_sequence


1001
2020-02-01
XXX.txt
1
1

1001
2020-01-01
XXX.txt
2
2

1001
2020-01-01
XXX.txt
3
2

1001
2020-02-01
XXX.txt
4
3

1001
2020-01-01
XXX.txt
5
4

2001
2020-02-01
YYY.txt
6
1

2001
2020-01-01
YYY.txt
7
2

2001
2020-01-01
YYY.txt
8
2

2001
2020-02-01
YYY.txt
9
3

2002
2020-02-01
YYY.txt
10
3

2001
2020-01-01
YYY.txt
11
4

3001
2020-01-01
ZZZ.txt
12
1

3001
2020-02-01
ZZZ.txt
13
2

3002
2020-02-01
ZZZ.txt
14
2

3001
2020-02-01
ZZZ.txt
15
2


with dataX as (
    select
        employee_code, movement_code, effective_date, filename, file_sequence,
        row_number() over (
            partition by filename, employee_code
            order by file_sequence) as process_sequence, /* file sequence is unique by itself */
        dense_rank() over (
            order by effective_date) as effective_date_sequence, /* I'm a bit skeptical that you needed the second ordering column */
        lag(effective_date) over (
            partition by filename
            order by file_sequence) as last_effective_date
    from hrcmn_emp_full_table
)
select *,
    sum(case when effective_date = last_effective_date then 0 else 1 end) over (
        partition by filename
        order by file_sequence) as final_process_sequence
from dataX
order by file_sequence;


employee_code
movement_code
effective_date
filename
file_sequence
process_sequence
effective_date_sequence
last_effective_date
final_process_sequence


1001
NEWHR
2020-02-01
XXX.txt
1
1
2
null
1

1001
NEWHR
2020-01-01
XXX.txt
2
2
1
2020-02-01
2

1001
NEWHR
2020-01-01
XXX.txt
3
3
1
2020-01-01
2

1001
NEWHR
2020-02-01
XXX.txt
4
4
2
2020-01-01
3

1001
NEWHR
2020-01-01
XXX.txt
5
5
1
2020-02-01
4

2001
NEWHR
2020-02-01
YYY.txt
6
1
2
null
1

2001
NEWHR
2020-01-01
YYY.txt
7
2
1
2020-02-01
2

2001
NEWHR
2020-01-01
YYY.txt
8
3
1
2020-01-01
2

2001
NEWHR
2020-02-01
YYY.txt
9
4
2
2020-01-01
3

2002
NEWHR
2020-02-01
YYY.txt
10
1
2
2020-02-01
3

2001
NEWHR
2020-01-01
YYY.txt
11
5
1
2020-02-01
4

3001
NEWHR
2020-01-01
ZZZ.txt
12
1
1
null
1

3001
NEWHR
2020-02-01
ZZZ.txt
13
2
2
2020-01-01
2

3002
NEWHR
2020-02-01
ZZZ.txt
14
1
2
2020-02-01
2

3001
NEWHR
2020-02-01
ZZZ.txt
15
3
2
2020-02-01
2


with dataX as (
    select *,
        row_number() over (
            partition by filename, employee_code
            order by file_sequence) as process_sequence_new, /* file sequence is unique by itself */
        dense_rank() over (
            order by effective_date, movement_code) as effective_date_sequence_new, /* I'm a bit skeptical that you need the second ordering column */
        lag(effective_date) over (
            partition by filename
            order by file_sequence) as last_effective_date
    from hrcmn_emp_full_table
), dataY as (
    select *,
        sum(case when effective_date = last_effective_date then 0 else 1 end) over (
            partition by filename
            order by file_sequence) as final_process_sequence_new
    from dataX
)
update dataY
set process_sequence = process_sequence_new,
    effective_date_sequence = effective_date_sequence_new,
    final_process_sequence = final_process_sequence_new;

select * from hrcmn_emp_full_table;


employee_code
movement_code
effective_date
filename
file_sequence
process_sequence
effective_date_sequence
final_process_sequence
MyGroup


1001
NEWHR
2020-02-01
XXX.txt
1
1
2
1
null

1001
NEWHR
2020-01-01
XXX.txt
2
2
1
2
null

1001
NEWHR
2020-01-01
XXX.txt
3
3
1
2
null

1001
NEWHR
2020-02-01
XXX.txt
4
4
2
3
null

1001
NEWHR
2020-01-01
XXX.txt
5
5
1
4
null

2001
NEWHR
2020-02-01
YYY.txt
6
1
2
1
null

2001
NEWHR
2020-01-01
YYY.txt
7
2
1
2
null

2001
NEWHR
2020-01-01
YYY.txt
8
3
1
2
null

2001
NEWHR
2020-02-01
YYY.txt
9
4
2
3
null

2002
NEWHR
2020-02-01
YYY.txt
10
1
2
3
null

2001
NEWHR
2020-01-01
YYY.txt
11
5
1
4
null

3001
NEWHR
2020-01-01
ZZZ.txt
12
1
1
1
null

3001
NEWHR
2020-02-01
ZZZ.txt
13
2
2
2
null

3002
NEWHR
2020-02-01
ZZZ.txt
14
1
2
2
null

3001
NEWHR
2020-02-01
ZZZ.txt
15
3
2
2
null

employee_code	effective_date	filename	file_sequence	final_process_sequence
1001	2020-02-01	XXX.txt	1	1
1001	2020-01-01	XXX.txt	2	2
1001	2020-01-01	XXX.txt	3	2
1001	2020-02-01	XXX.txt	4	3
1001	2020-01-01	XXX.txt	5	4
2001	2020-02-01	YYY.txt	6	1
2001	2020-01-01	YYY.txt	7	2
2001	2020-01-01	YYY.txt	8	2
2001	2020-02-01	YYY.txt	9	3
2002	2020-02-01	YYY.txt	10	3
2001	2020-01-01	YYY.txt	11	4
3001	2020-01-01	ZZZ.txt	12	1
3001	2020-02-01	ZZZ.txt	13	2
3002	2020-02-01	ZZZ.txt	14	2
3001	2020-02-01	ZZZ.txt	15	2

employee_code	movement_code	effective_date	filename	file_sequence	process_sequence	effective_date_sequence	last_effective_date	final_process_sequence
1001	NEWHR	2020-02-01	XXX.txt	1	1	2	null	1
1001	NEWHR	2020-01-01	XXX.txt	2	2	1	2020-02-01	2
1001	NEWHR	2020-01-01	XXX.txt	3	3	1	2020-01-01	2
1001	NEWHR	2020-02-01	XXX.txt	4	4	2	2020-01-01	3
1001	NEWHR	2020-01-01	XXX.txt	5	5	1	2020-02-01	4
2001	NEWHR	2020-02-01	YYY.txt	6	1	2	null	1
2001	NEWHR	2020-01-01	YYY.txt	7	2	1	2020-02-01	2
2001	NEWHR	2020-01-01	YYY.txt	8	3	1	2020-01-01	2
2001	NEWHR	2020-02-01	YYY.txt	9	4	2	2020-01-01	3
2002	NEWHR	2020-02-01	YYY.txt	10	1	2	2020-02-01	3
2001	NEWHR	2020-01-01	YYY.txt	11	5	1	2020-02-01	4
3001	NEWHR	2020-01-01	ZZZ.txt	12	1	1	null	1
3001	NEWHR	2020-02-01	ZZZ.txt	13	2	2	2020-01-01	2
3002	NEWHR	2020-02-01	ZZZ.txt	14	1	2	2020-02-01	2
3001	NEWHR	2020-02-01	ZZZ.txt	15	3	2	2020-02-01	2