Thiết kế website giá rẻ

Question

I am looking for the support to find the sequence number based on the employee, effective date and the order which the data received in the file.
Same employee, effective date combination can come any row. same serial number needs to allocate for the consecutive rows. And the serial number should be continuous

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')

;with processnum as (select filename,[file_sequence],[employee_code],[effective_date],
     ROW_NUMBER() OVER (partition BY filename,[employee_code] ORDER BY [file_sequence],[effective_date]) as processnum      
     from hrcmn_emp_full_table with (nolock))  
      
 update tmp  
 set    process_sequence            = num.processnum
 from   hrcmn_emp_full_table tmp,
        processnum num
 where  tmp.filename        = num.filename
 and    tmp.[file_sequence]         = num.[file_sequence]
 and    tmp.[employee_code] = num.[employee_code]
 and    tmp.[effective_date]        = num.[effective_date]
 and    tmp.process_sequence is null


 ;with processnum as (select filename,employee_code,file_sequence,a.[movement_code],[effective_date],
     dense_rank() OVER (ORDER BY [effective_date],a.movement_code) as rank_function_mov
     from hrcmn_emp_full_table a with (nolock))
   
 update tmp  
 set    effective_date_sequence     = num.rank_function_mov
 from   hrcmn_emp_full_table tmp,
        processnum num
 where  tmp.filename        = num.filename
 and    tmp.[movement_code]         = num.[movement_code] 
 and    tmp.[effective_date]        = num.[effective_date]
 and    tmp.employee_code           = num.employee_code
 and    tmp.file_sequence           = num.file_sequence
 and    tmp.effective_date_sequence is null

  
DECLARE @MyGroup INT
    SET @MyGroup = 0
DECLARE @LastProcess_Sequence DECIMAL(6,2)
    SET @LastProcess_Sequence = -1
    
    --updating consecutive group
    UPDATE a
    SET @MyGroup = MyGroup = CASE
                               WHEN a.Process_Sequence = @LastProcess_Sequence + 1
                               THEN @MyGroup
                               ELSE @MyGroup+1
                             END,
        @LastProcess_Sequence = a.Process_Sequence
   FROM hrcmn_emp_full_table a,
        ( SELECT DISTINCT t1.filename,t1.file_sequence,
        t1.Effective_date_sequence,t1.Process_Sequence
        FROM hrcmn_emp_full_table t1,
        hrcmn_emp_full_table t2
        WHERE (t1.Process_Sequence+1 = t2.Process_Sequence)
        AND t1.Effective_date_sequence   = t2.Effective_date_sequence
        AND t1.filename = t2.filename
        UNION 
        SELECT DISTINCT t1.filename,t1.file_sequence,
        t1.Effective_date_sequence,t1.Process_Sequence
        FROM hrcmn_emp_full_table t1,
        hrcmn_emp_full_table t2
        WHERE (t1.Process_Sequence-1 = t2.Process_Sequence)
        AND t1.Effective_date_sequence   = t2.Effective_date_sequence
        AND t1.filename = t2.filename) w
  where a.file_sequence = w.file_sequence
   and a.Effective_date_sequence = w.Effective_date_sequence
   and a.Process_Sequence = w.Process_Sequence
   AND a.filename = w.filename


 update a
 set final_process_sequence = T.final
 from hrcmn_emp_full_table a , 
             (select min(process_sequence) 'final',effective_date_sequence,filename
             from hrcmn_emp_full_table
             where MyGroup is null
             group by effective_date_sequence,filename) T
 where a.effective_date_sequence = T.effective_date_sequence
 and a.filename = T.filename
 and a.MyGroup is null


 update a
 set    final_process_sequence = T.final
 from   hrcmn_emp_full_table a , 
     (select min(process_sequence) 'final',effective_date_sequence,MyGroup,filename
     from   hrcmn_emp_full_table with (nolock)
      where MyGroup is not null
     group by effective_date_sequence,MyGroup,filename) T
 where a.effective_date_sequence = T.effective_date_sequence
 and a.MyGroup is not null
 and a.MyGroup = T.MyGroup
 and a.filename = T.filename

I am getting the below output,

However, expecting the below output.

Could anyone support in this?

Thiết kế website giá rẻ

Danh mục

SQL query to update serial number of consecutive values