Update date based on second highest date

Question

Hi,

I want to change the second highest Effective start date to current recored date where current indicator is 'Y'

Sample data as below.

I need to data as expected below after chnage the date.

Thank You

Answer 1

Hi @Raj0125

You can try this.

create table test(
R_Id int,
Current_Indicator char(1),
Deleted_Indicator char(1),
Effective_Start_Date datetime,
Effective_End_Date datetime,
Inserted_Date datetime,
Updated_Date datetime);

insert into test values
(3041888,'Y','N','2023-05-26 07:47:06','2099-12-31 00:00:00','2023-05-26 07:47:06','2023-05-26 07:47:06'),
(3041888,'N','N','2021-03-31 02:05:17','2021-04-01 12:49:37','2021-03-31 02:05:17','2021-04-01 12:49:37'),
(3041888,'N','N','2021-04-01 12:49:41','2023-05-26 07:47:02','2021-04-01 12:49:43','2023-05-26 07:47:02')

;with T1 as(
  select Effective_Start_Date as ESD,Effective_End_Date as EED,
         row_number()over(order by Effective_Start_Date) as num from test
),T2 as(
  select * from T1 where num = 2)
update test set Effective_Start_Date = ESD,
                Inserted_Date = ESD,
				Updated_Date = ESD 
from test as A cross join T2 as B where A.Current_Indicator = 'Y';

select * from test;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.