SolutionForQandAQuestion20220509.sql

 IF(OBJECT_ID('tempdb..#trans') IS NOT NULL) DROP TABLE #trans;
      create table #trans (acct_nbr varchar(128), tran_dt datetime, tran_amt decimal(38,2), tran_type1 varchar(128), tran_type2 varchar(128), tran_id varchar(128), tran_id_decline varchar(128))
        
  insert into #trans (acct_nbr, tran_dt, tran_amt, tran_type1, tran_type2, tran_id
  )
  values 
  ('111', '2022-05-08 11:08:47.257', '1.00', 'C', 'X', 'ID-01'),
  ('111', '2022-05-08 11:08:47.257', '1.00', 'A', '', 'ID-02'),
  ('111', '2022-05-09 11:10:47.257', '1.00', 'A', '', 'ID-03'),
  ('111', '2022-05-09 11:11:11.257', '1.00', 'P', 'O', 'ID-04'),
  ('111', '2022-05-09 13:11:33.123', '1.00', 'A', 'X', 'ID-05'),
  ('111', '2022-05-09 14:10:47.257', '1.00', 'A', '', 'ID-06'),
  ('111', '2022-05-09 14:11:17.257', '1.00', 'P', 'O', 'ID-07'),
  ('111', '2022-05-09 14:11:48.257', '1.00', 'P', 'O', 'ID-08') ,
  ('222', '2022-05-10 11:09:47.257', '1.00', 'A', '', 'ID-09'),
  ('222', '2022-05-10 11:09:47.257', '1.00', 'A', '', 'ID-10'),
  ('222', '2022-05-10 11:09:57.257', '1.00', 'P', 'O', 'ID-11'),
  ('222', '2022-05-10 11:09:57.257', '1.00', 'P', 'O', 'ID-12'),
  ('333', '2022-05-10 11:09:47.257', '1.00', 'A', '', 'ID-13'),
  ('333', '2022-05-10 11:09:47.257', '1.00', 'A', '', 'ID-14'),
  ('333', '2022-05-10 11:09:57.257', '1.00', 'P', 'O', 'ID-15');
        
         
  IF(OBJECT_ID('tempdb..#tran_decline_notice') IS NOT NULL) DROP TABLE #tran_decline_notice;
  select * into #tran_decline_notice from (select * from #trans where tran_type1 = 'P' and tran_type2 = 'O') a
    
  ;with mycte as (
    
 select t.acct_nbr    ,t.tran_type1,    t.tran_amt, t.tran_id , d.tran_id as tran_id_decline
  , ROW_NUMBER()OVER ( PARTITION by t.acct_nbr,    t.tran_amt,t.tran_type1,t.tran_id Order BY t.tran_id  ) rn1 
     
    
      from  #trans t
      left join #tran_decline_notice d
      on t.acct_nbr = d.acct_nbr and t.tran_amt = d.tran_amt
      where DATEDIFF(ss,t.tran_dt,d.tran_dt) between 0 and 60 
      and t.tran_type1 = 'A' 
      and d.tran_type1 = 'P' 
      and d.tran_type2 = 'O'
      )
      ,mycte1 as (
      select  acct_nbr    , tran_type1, tran_amt,  tran_id ,  tran_id_decline, rn1,
   ROW_NUMBER()OVER ( PARTITION by  acct_nbr, tran_amt, tran_type1,tran_id_decline Order BY  cast(right(tran_id_decline,2) as int), rn1  ) rn2 
     
  from mycte
   )
  ,mycte2 as (
  select  acct_nbr    , tran_type1, tran_amt,  tran_id ,  tran_id_decline  
  from mycte1
  where rn2=1
 )
     
    
      Merge #trans t
       using mycte2 m on m.tran_id=t.tran_id  
       WHen matched then
       Update 
       Set   tran_id_decline = m.tran_id_decline;
    
  select * from  #trans
    
  /*
  acct_nbr    tran_dt    tran_amt    tran_type1    tran_type2    tran_id    tran_id_decline
 111    2022-05-08 11:08:47.257    1.00    C    X    ID-01    NULL
 111    2022-05-08 11:08:47.257    1.00    A        ID-02    NULL
 111    2022-05-09 11:10:47.257    1.00    A        ID-03    ID-04
 111    2022-05-09 11:11:11.257    1.00    P    O    ID-04    NULL
 111    2022-05-09 13:11:33.123    1.00    A    X    ID-05    NULL
 111    2022-05-09 14:10:47.257    1.00    A        ID-06    ID-07
 111    2022-05-09 14:11:17.257    1.00    P    O    ID-07    NULL
 111    2022-05-09 14:11:48.257    1.00    P    O    ID-08    NULL
 222    2022-05-10 11:09:47.257    1.00    A        ID-09    ID-11
 222    2022-05-10 11:09:47.257    1.00    A        ID-10    ID-12
 222    2022-05-10 11:09:57.257    1.00    P    O    ID-11    NULL
 222    2022-05-10 11:09:57.257    1.00    P    O    ID-12    NULL
 333    2022-05-10 11:09:47.257    1.00    A        ID-13    ID-15
 333    2022-05-10 11:09:47.257    1.00    A        ID-14    NULL
 333    2022-05-10 11:09:57.257    1.00    P    O    ID-15    NULL
    
  */