Update record from previous Effec_strt_date

Question

Hi,

Below is the sample data its fetching.

I want to chnage the data like below.First record of Effectiver_start_date ,Inserted_Date ,Updated_Date values should be from below record Effective_Start_Date value where Current_Ind ='N' for each S_id

Please advice.

Thanks in advance.

Answer 1

Hi @Raj0125

Try this query:

;WITH CTE AS
(
 SELECT *,MAX(CASE WHEN Current_Ind='N' THEN Effective_Start_Date ELSE NULL END)OVER(PARTITION BY S_Id)AS New_Value
 FROM TableName 
)
UPDATE CTE SET Effective_Start_Date=New_Value, Inserted_Date=New_Value, Updated_Date=New_Value
WHERE Current_Ind='Y'

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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.