Post Undeleted by Andriy M
2 corrected code, added output
source | link
SELECT
  ID,
  Surname   = MAX(CASE [Date] WHEN LastSurnameDate   THEN Surname   END) OVER (PARTITION BY ID, LastSurnameDate   ORDER BY [Date] ASC),
  FirstName = MAX(CASE [Date] WHEN LastFirstNameDate THEN FirstName END) OVER (PARTITION BY ID, LastFirstNameDate ORDER BY [Date] ASC),
  Office    = MAX(CASE [Date] WHEN LastOfficeDate    THEN Office    END) OVER (PARTITION BY ID, LastOfficeDate    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 LastValuess
;

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

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, LastSurnameDate   ORDER BY [Date] ASC),
  FirstName = MAX(CASE [Date] WHEN LastFirstNameDate THEN FirstName END) OVER (PARTITION BY ID, LastFirstNameDate ORDER BY [Date] ASC),
  Office    = MAX(CASE [Date] WHEN LastOfficeDate    THEN Office    END) OVER (PARTITION BY ID, LastOfficeDate    ORDER BY [Date] ASC),
  [Date]
FROM
  LastValues
;

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

Output:

ID          Surname FirstName Office    Date
----------- ------- --------- --------- ----------
1           Smith   Bill      Melbourne 2015-01-01
1           Smith   Bill      Sydney    2015-03-01
2           Brown   Mary      Melbourne 2014-04-01
2           Jones   Mary      Adelaide  2014-05-01
2           Jones   Mary      Sydney    2015-01-01
2           Jones   Mary      Perth     2015-03-01
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.

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.

SELECT
  ID,
  Surname   = MAX(Surname  ) OVER (PARTITION BY ID, LastSurnameDate   ORDER BY [Date] ASC),
  FirstName = MAX(FirstName) OVER (PARTITION BY ID, LastFirstNameDate ORDER BY [Date] ASC),
  Office    = MAX(Office   ) OVER (PARTITION BY ID, LastOfficeDate    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 s
;

ORDER BY is not necessary in the second step but it can reduce the search range.

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(Surname  ) OVER (PARTITION BY ID, LastSurnameDate   ORDER BY [Date] ASC),
  FirstName = MAX(FirstName) OVER (PARTITION BY ID, LastFirstNameDate ORDER BY [Date] ASC),
  Office    = MAX(Office   ) OVER (PARTITION BY ID, LastOfficeDate    ORDER BY [Date] ASC),
  [Date]
FROM
  LastValues
;

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

Output:

ID          Surname FirstName Office    Date
----------- ------- --------- --------- ----------
1           Smith   Bill      Melbourne 2015-01-01
1           Smith   Bill      Sydney    2015-03-01
2           Brown   Mary      Melbourne 2014-04-01
2           Jones   Mary      Adelaide  2014-05-01
2           Jones   Mary      Sydney    2015-01-01
2           Jones   Mary      Perth     2015-03-01
    Post Deleted by Andriy M
1
source | link

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.