WITH AuditData AS (
    SELECT
        audit.objectid,
        audit.objectidname,
        -- We get the prevEmail from the shredded JSON data below
        json_attributes.oldValue AS 'prevEmail', 
        contact.emailaddress1,
        audit.useridname,
        audit.createdon
    FROM
        audit
    INNER JOIN
        contact ON audit.objectid = contact.contactid
    -- Use CROSS APPLY to parse the JSON array for each row in the audit table
    CROSS APPLY OPENJSON(audit.changedata, '$.changedAttributes')
        WITH (
            logicalName NVARCHAR(128) '$.logicalName',
            oldValue    NVARCHAR(MAX)  '$.oldValue'
        ) AS json_attributes
    WHERE
        audit.createdon >= '2025-07-20'
        AND audit.attributemask LIKE '%42%'
        AND audit.objectidtype = 'contact'
        AND (contact.emailaddress1 = '' OR contact.emailaddress1 IS NULL)
        -- Filter the shredded JSON to find only the email address change
        AND json_attributes.logicalName = 'emailaddress1'
),
RankedAudits AS (
    SELECT
        objectid AS 'contactId',
        objectidname AS 'contactName',
        prevEmail,
        emailaddress1 AS 'Email',
        useridname AS 'modifiedBy',
        createdon,
        ROW_NUMBER() OVER(PARTITION BY objectid ORDER BY createdon DESC) as 'rowNum'
    FROM
        AuditData
)
SELECT
    contactId,
    contactName,
    prevEmail,
    Email,
    modifiedBy,
    createdon
FROM
    RankedAudits
WHERE
    prevEmail IS NOT NULL 
    AND prevEmail != '' -- Also good to exclude empty strings
    AND rowNum = 1
ORDER BY
    createdon DESC;