CREATE TABLE dbo.MainTable
(
    DateHourStamp DATETIME NOT NULL,
    RecordIdentifier NCHAR(17) NOT NULL,
    LatestStatusDateTime DATETIME NULL,
    LatestStatusValue NCHAR(2) NULL,
    ...
)

ALTER TABLE dbo.MainTable
ADD CONSTRAINT PK_MainTable
PRIMARY KEY CLUSTERED 
(
    DateHourStamp ASC,
    RecordIdentifier ASC
)

CREATE UNIQUE NONCLUSTERED INDEX IX_MainTable_RecordIdentifier
ON dbo.MainTable
(
    RecordIdentifier ASC
)

CREATE TABLE dbo.StatusTable
(
    RecordIdentifier NCHAR(17) NOT NULL,
    StatusDateTime DATETIME NOT NULL,
    StatusRank TINYINT NOT NULL,
    StatusValue NCHAR(2) NOT NULL
)

ALTER TABLE dbo.StatusTable
ADD CONSTRAINT PK_StatusTable
PRIMARY KEY CLUSTERED
(
    RecordIdentifier ASC,
    StatusDateTime DESC,
    StatusRank DESC
)

WITH NewStatuses AS
(
    SELECT
        RecordIdentifier,
        StatusDateTime,
        StatusValue,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                RecordIdentifier
            ORDER BY
                StatusDateTime DESC,
                StatusRank DESC
        )
        AS RowNumber
    FROM
        dbo.StatusTable
)
UPDATE
    dbo.MainTable
SET
    LatestStatusDateTime = NewStatuses.StatusDateTime,
    LatestStatusValue = NewStatuses.StatusValue
FROM
    dbo.MainTable
INNER JOIN
    NewStatuses
    ON (MainTable.RecordIdentifier = NewStatuses.RecordIdentifier)
WHERE
    NewStatuses.RowNumber = 1;