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;

-- Step 1: Create a temporary table to store the results of your audit query.
-- This isolates the data we need to work with.
CREATE TABLE #EmailUpdates (
    contactId UNIQUEIDENTIFIER,
    contactName NVARCHAR(255),
    prevEmail NVARCHAR(255),
    Email NVARCHAR(255),
    modifiedBy NVARCHAR(255),
    createdon DATETIME
);

-- Step 2: Insert the data from your query into the temporary table.
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
)
INSERT INTO #EmailUpdates (contactId, contactName, prevEmail, Email, modifiedBy, createdon)
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;

-- Step 3: Preview the changes.
-- Run the SELECT statement below to see the current data versus the proposed new data.
SELECT
    c.contactid,
    c.emailaddress1 AS 'CurrentEmail',
    upd.prevEmail AS 'NewEmail'
FROM
    contact c
INNER JOIN
    #EmailUpdates upd ON c.contactid = upd.contactId;

-- Step 4: If you are satisfied with the preview, run the UPDATE statement below.
/* -- Uncomment the following lines to execute the update
UPDATE
    c
SET
    emailaddress1 = upd.prevEmail
FROM
    contact c
INNER JOIN
    #EmailUpdates upd ON c.contactid = upd.contactId;

-- Check the number of rows affected to confirm the update was successful.
-- We must convert the number @@ROWCOUNT to a string to print it.
PRINT CONVERT(VARCHAR, @@ROWCOUNT) + ' rows updated.';

-- Step 5: Clean up the temporary table.
DROP TABLE #EmailUpdates;
*/