Result set with multiple rows based on max date and not NULL
SELECT DISTINCT StaffCode, 
       FIRST_VALUE(FirstName) 
           OVER (PARTITION BY StaffCode 
                 ORDER BY CASE WHEN FirstName IS NOT NULL 
                               THEN EffectiveDate 
                               END DESC) FirstName, 
       FIRST_VALUE(LastName)  
           OVER (PARTITION BY StaffCode  
                 ORDER BY CASE WHEN LastName IS NOT NULL  
                               THEN EffectiveDate 
                               END DESC) LastName, 
       FIRST_VALUE(MarriageStatus)  
           OVER (PARTITION BY StaffCode  
                 ORDER BY CASE WHEN MarriageStatus IS NOT NULL  
                               THEN EffectiveDate 
                               END DESC) MarriageStatus
FROM SourceTable;