Return to Revisions

You could use a window MAX() to find the date of the last column value so far, for each column:

SELECT
  *,
  LastSurnameDate   = MAX(CASE WHEN Surname   IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  LastFirstNameDate = MAX(CASE WHEN FirstName IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  LastOfficeDate    = MAX(CASE WHEN Office    IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC)
FROM
  dbo.Staff

The next, and last, step would be to use a window MAX() again to look up the values from the corresponding rows:

SELECT
  ID,
  Surname   = MAX(CASE [Date] WHEN LastSurnameDate   THEN Surname   END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  FirstName = MAX(CASE [Date] WHEN LastFirstNameDate THEN FirstName END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  Office    = MAX(CASE [Date] WHEN LastOfficeDate    THEN Office    END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  [Date]
FROM
  (
    SELECT
      *,
      LastSurnameDate   = MAX(CASE WHEN Surname   IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
      LastFirstNameDate = MAX(CASE WHEN FirstName IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
      LastOfficeDate    = MAX(CASE WHEN Office    IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC)
    FROM
      dbo.Staff
  ) AS LastValues
;

ORDER BY is not necessary in the second step but it does reduce the search range for the most part.

Same method but using a CTE instead of a derived table:

WITH LastValues AS
  (
    SELECT
      *,
      LastSurnameDate   = MAX(CASE WHEN Surname   IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
      LastFirstNameDate = MAX(CASE WHEN FirstName IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
      LastOfficeDate    = MAX(CASE WHEN Office    IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC)
    FROM
      dbo.Staff
  )
SELECT
  ID,
  Surname   = MAX(CASE [Date] WHEN LastSurnameDate   THEN Surname   END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  FirstName = MAX(CASE [Date] WHEN LastFirstNameDate THEN FirstName END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  Office    = MAX(CASE [Date] WHEN LastOfficeDate    THEN Office    END) OVER (PARTITION BY ID ORDER BY [Date] ASC),
  [Date]
FROM
  LastValues
;

The difference would be only in syntax and not in performance or results.