DROP VIEW IF EXISTS JobStatus
GO
Create View JobStatus
as
WITH LatestChanges AS (
    SELECT
        a.CompanyID,
        SUBSTRING(a.CombinedKey, 3, LEN(a.CombinedKey) - 2) AS ContractCD,
        a.ChangeDate,
        a.ModifiedFields,
        ROW_NUMBER() OVER (
            PARTITION BY 
                a.CompanyID, 
                SUBSTRING(a.CombinedKey, 3, LEN(a.CombinedKey) - 2)
            ORDER BY 
                a.ChangeDate DESC
        ) AS RowNum
    FROM
        AuditHistory a
    WHERE
        a.TableName = 'Contract'
        AND a.ModifiedFields LIKE 'AttributeJOBSTATUS%'
)
SELECT
    CompanyID,
    ContractCD,
    ChangeDate AS MostRecentChangeDate,
    'AttributeJOBSTATUS' AS FieldName,
    LTRIM(
        CASE
            WHEN ModifiedFields LIKE 'AttributeJOBSTATUS %' THEN
                STUFF(ModifiedFields, 1, LEN('AttributeJOBSTATUS') + 1, '')
            ELSE
                STUFF(ModifiedFields, 1, LEN('AttributeJOBSTATUS'), '')
        END
    ) AS FieldValue
FROM
    LatestChanges
WHERE
    RowNum = 1;