SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT V.*
INTO dbo.Activity
FROM (VALUES(1,'A',CONVERT(date,NULL)),
            (1,'B',CONVERT(date,NULL)),
            (1,'C',CONVERT(date,NULL)),
            (1,'D',CONVERT(date,NULL)))V(ItemID ,ActivityID,ActivityDate);

SELECT *
INTO dbo.Status
FROM (VALUES(1,'X',CONVERT(date,'01-03-2024',103)),--Please do not use ambiguous dates
            (1,'Y',CONVERT(date,'15-03-2024',103)),--Please do not use ambiguous dates
            (1,'Z',CONVERT(date,'02-04-2024',103)) )V(ItemID,StatusID,StatusDate)--Please do not use ambiguous dates
7 rows affected

UPDATE A
SET A.ActivityDate = SD.StatusDate
FROM dbo.Activity A
     OUTER APPLY (SELECT MAX(CASE WHEN s.StatusID='X' AND V.ActivityID='A' THEN s.StatusDate
                                  WHEN s.StatusID='Y' AND V.ActivityID IN ('B','C') THEN s.StatusDate
                                  WHEN s.StatusID='Z' AND V.ActivityID='D' THEN s.StatusDate
                             END) AS StatusDate
                 FROM dbo.Status S
                      CROSS APPLY (VALUES(a.ActivityID))V(ActivityID) --Gets around the outer reference
                 WHERE A.ItemID = S.ItemID) SD;
Warning: Null value is eliminated by an aggregate or other SET operation.
4 rows affected

SELECT *
FROM dbo.Activity;


ItemID
ActivityID
ActivityDate


1
A
2024-03-01

1
B
2024-03-15

1
C
2024-03-15

1
D
2024-04-02


DROP TABLE dbo.Activity;
DROP TABLE dbo.Status;