(SELECT 
    d.tenantid AS TenantId,
    t.name AS Tenant,
    d.clientid AS ClientId,
    cl.commonname AS Client,
    c.claimtype,
    c.reasontype,
    c.reason,
    d.claimid AS ClaimID,
    d.disputeid AS DisputeID,
    (CASE
        when d.acquirernetwork = 'MasterCard' THEN 'Mastercard'
        when d.acquirernetwork = 'VISA' THEN 'Visa'
        else d.acquirernetwork
    END) AS Network,
    e.recoveryassociation AS CollaborationNetwork,
    d.merchantcategorycode AS MCC,
    d.merchantname AS Merchant,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000')AS ResolvedDate,
    INITCAP(e.actionname) AS Eligibility,
    TO_TIMESTAMP_TZ(r1.performedondatetime::varchar || ' +0000')AS AttemptDate,
    (CASE
        WHEN r2.actionname IS NOT NULL THEN 'Response Received'
        WHEN AttemptDate IS NOT NULL THEN 'No Response'
    END) AS ResponseStatus,
    r2.actionname AS Response,
    TO_TIMESTAMP_TZ(r2.performedondatetime::varchar || ' +0000')AS ResponseDate,
    (CASE
        WHEN AttemptDate IS NOT NULL AND ResponseDate IS NOT NULL THEN DATEDIFF(h, AttemptDate, ResponseDate) -- only evaluate response time when there is an attempt and a response
    END) AS ResponseTime,
    (CASE
        WHEN AttemptDate IS NOT NULL and r2.actionname IS NULL THEN 'No Response'
        ELSE m.outcome
    END) AS CollaborationOutcome,
    (CASE
        WHEN CollaborationOutcome = 'Accepted' AND (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid AND mc.appliedamount !=0) !=0 THEN 'Recovered' -- only associate credits with collaboration requests that are accepted.  For clients that use both networks, there will typically only be an "Accept" response via one of the two networks
        WHEN CollaborationOutcome = 'Accepted' THEN 'Not Recovered'
    END) AS RecoveryStatus,
    (CASE
        WHEN CollaborationOutcome = 'Accepted' THEN (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid AND mc.appliedamount !=0) -- only associate credits with collaboration requests that are accepted.  For clients that use both networks, there will typically only be an "Accept" response via one of the two networks
    END) AS MerchantCreditAmount,
    (CASE
        WHEN CollaborationOutcome = 'Accepted' THEN (SELECT MAX(mc.eventdatetime) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid AND mc.appliedamount !=0) -- only associate credits with collaboration requests that are accepted.  For clients that use both networks, there will typically only be an "Accept" response via one of the two networks
    END) AS MerchantCreditDate,
    (CASE
        WHEN ResponseDate IS NOT NULL AND MerchantCreditDate IS NOT NULL THEN DATEDIFF(h, ResponseDate, MerchantCreditDate) -- only evaluate credit time if there was a response and a merchant credit
    END) AS CreditTime
FROM dispute AS d
JOIN (select row_number() over (partition by tenantid, disputeid, stage, actionname order by performedondatetime asc) as rn,* from recoveryaction) as e on e.tenantid = d.tenantid AND e.disputeid = d.disputeid AND e.recoveryassociation = 'Verifi' AND e.stage = 'Collaboration Eligibility' AND e.rn = 1
JOIN tenant AS t ON t.tenantid = d.tenantid
JOIN client AS cl ON cl.tenantid = d.tenantid AND cl.clientid = d.clientid
JOIN claim AS c ON c.tenantid = d.tenantid AND c.claimid = d.claimid
LEFT JOIN recoveryaction AS r1 ON r1.tenantid = d.tenantid AND r1.disputeid = d.disputeid AND r1.stage = 'Collaboration Initiated' AND r1.recoveryassociation = 'Verifi'
LEFT JOIN recoveryaction AS r2 ON r2.tenantid = d.tenantid AND r2.disputeid = d.disputeid AND r2.stage = 'Collaboration Response' AND r2.recoveryassociation = 'Verifi'
LEFT JOIN collaborationlookup AS m ON m.response = r2.actionname
ORDER BY d.tenantid ASC, d.claimid DESC, d.disputeid DESC)