how to update the columns based on most recent date in sql server

Question

HI i have a query

i need to update my target table records from stage table fields

field names : partnerid ,firstname,lastname,manager,effectivedate

query : based on effective date is null then i will update the above columns.

else condition : if date is column is not null then i need to go for the most recent effective date in my date column based

please give us the query how to write this update logic for the else condition

example :

Effectivedate

---

NULL

2022-08-13

2022-03-23 Update query

Update U
Set Lastname=C.[LAST_NAME],
    FirstName=C.[FIRST_NAME],
   FullName=C.FULL_NAME,
   EMailAddress=C.[EMAIL_ADDRESS],
   PersonNumber=C.[PERSON_NUMBER],
   [Manager]=C.Manager,
   EffectiveFromDate=C.EffectiveFromDate,
   EffectiveToDate=C.EffectiveToDate,
   DeptNumber=C.DeptNumber,
   UserStatus=C.UserStatus,
   Division=C.Division,
   Client=C.Client_MANDT
     	 
From [Stage].[dbo].[Stg_USER] C 
Inner join Dim.dbo.User U on C.PERSON_ID=U.PersonID
where C.[EffectiveToDate] is null

this above is my table stature, and my expected condition is Effectivedate is not null then i need to pick the most recent date values and update the target tables.

NULL
2022-08-13
2022-03-23

Answer 1

You can use a subquery to get the most recent effective date and then use a CASE statement in your UPDATE statement to check if the effective date is null or not. Here's an example:

UPDATE U
SET Lastname = C.[LAST_NAME],
    FirstName = C.[FIRST_NAME],
    FullName = C.FULL_NAME,
    EMailAddress = C.[EMAIL_ADDRESS],
    PersonNumber = C.[PERSON_NUMBER],
    [Manager] = CASE
                   WHEN C.EffectiveFromDate = (SELECT MAX(EffectiveFromDate) 
                                               FROM [Stage].[dbo].[Stg_USER] 
                                               WHERE PERSON_ID = C.PERSON_ID 
                                                 AND EffectiveFromDate < GETDATE())
                   THEN C.Manager
                   ELSE U.[Manager]
                 END,
    EffectiveFromDate = CASE 
                           WHEN C.EffectiveFromDate = (SELECT MAX(EffectiveFromDate) 
                                                       FROM [Stage].[dbo].[Stg_USER] 
                                                       WHERE PERSON_ID = C.PERSON_ID 
                                                         AND EffectiveFromDate < GETDATE())
                           THEN C.EffectiveFromDate
                           ELSE U.EffectiveFromDate
                         END,
    EffectiveToDate = C.EffectiveToDate,
    DeptNumber = C.DeptNumber,
    UserStatus = C.UserStatus,
    Division = C.Division,
    Client = C.Client_MANDT
FROM Dim.dbo.User U 
INNER JOIN [Stage].[dbo].[Stg_USER] C 
    ON C.PERSON_ID = U.PersonID
WHERE C.[EffectiveToDate] IS NULL 
   OR C.EffectiveFromDate = (SELECT MAX(EffectiveFromDate) 
                             FROM [Stage].[dbo].[Stg_USER] 
                             WHERE PERSON_ID = C.PERSON_ID 
                               AND EffectiveFromDate < GETDATE())

This query will update the target table records from the stage table fields based on the conditions you mentioned in your question. The subquery in the CASE statement will get the most recent effective date for the same person ID and check if it's the same as the current effective date in the stage table. If it is, then it will update the Manager and EffectiveFromDate fields with the values from the stage table, otherwise, it will keep the values from the target table.