I haven't seen anyone mention LAST_VALUE, yet.

SELECT

mt.Applicants_ID,

mt.APPL_CURRENT_STATUS,

mt.APPL_CURRENT_STATUS_DATE,

LAST_VALUE(mt.APPL_CURRENT_STATUS) OVER(PARTITION BY mt.Applicants_ID ORDER BY mt.APPL_CURRENT_STATUS_DATE ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lv

FROM #myTable mt

I use ROW_NUMBER() if I want to limit the output to the last row and LAST_VALUE if I want to spread the value across multiple rows.

Drew