(SELECT 
	d.clientid AS "Client ID",    
	d.claimid AS "Claim ID",
    d.disputeid AS "Dispute ID",
    d.acquirernetwork AS "Network",
    e.recoveryassociation AS "Collaboration Network",
    d.merchantcategorycode AS "MCC",
    d.merchantname AS "Merchant",
    d.resolveddatetime AS "Resolved Date",
    INITCAP(e.actionname) AS "Eligibility",
    r1.performedondatetime AS "Attempt Date",
    (CASE
        WHEN r2.actionname IS NOT NULL THEN 'Response Received'
        WHEN "Attempt Date" IS NOT NULL THEN 'No Response'
    END) AS "Response Status",
    r2.actionname AS "Response",
    r2.performedondatetime AS "Response Date",
    (CASE
        WHEN "Attempt Date" IS NOT NULL AND "Response Date" IS NOT NULL THEN DATEDIFF(h, "Attempt Date", "Response Date") -- only evaluate response time when there is an attempt and a response
    END) AS "Response Time",
    (CASE
        WHEN "Attempt Date" IS NOT NULL and r2.actionname IS NULL THEN 'No Response'
        ELSE m.outcome
    END) AS "Outcome",
    (CASE
        WHEN "Outcome" = '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 "Outcome" = 'Accepted' THEN 'Not Recovered'
    END) AS "Recovery Status",
    (CASE
        WHEN "Outcome" = '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 "Merchant Credit Amount",
    (CASE
        WHEN "Outcome" = '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 "Merchant Credit Date",
    (CASE
        WHEN "Response Date" IS NOT NULL AND "Merchant Credit Date" IS NOT NULL THEN DATEDIFF(h, "Response Date", "Merchant Credit Date") -- only evaluate credit time if there was a response and a merchant credit
    END) AS "Credit Time"
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
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
WHERE
    d.tenantid = 'YOURTENANTID'
    AND d.resolveddatetime =:daterange
ORDER BY
    d.tenantid ASC, d.claimid DESC, d.disputeid DESC)
    
UNION

(SELECT 
    d.clientid AS "Client ID",
    d.claimid AS "Claim ID",
    d.disputeid AS "Dispute ID",
    d.acquirernetwork AS "Network",
    e.recoveryassociation AS "Collaboration Network",
    d.merchantcategorycode AS "MCC",
    d.merchantname AS "Merchant",
    d.resolveddatetime AS "Resolved Date",
    INITCAP(e.actionname) AS "Eligibility",
    e.performedondatetime AS "Attempt Date",
    (CASE
        WHEN r2.actionname IS NOT NULL THEN 'Response Received'
        WHEN "Attempt Date" IS NOT NULL THEN 'No Response'
    END) AS "Response Status",
    r2.actionname AS "Response",
    r2.performedondatetime AS "Response Date",
    (CASE
        WHEN "Attempt Date" IS NOT NULL AND "Response Date" IS NOT NULL THEN DATEDIFF(h, "Attempt Date", "Response Date") -- only evaluate response time when there is an attempt and a response
    END) AS "Response Time",
    (CASE
        WHEN "Attempt Date" IS NOT NULL and r2.actionname IS NULL THEN 'No Response'
        ELSE m.outcome
    END) AS "Outcome",
    (CASE
        WHEN "Outcome" = '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 "Outcome" = 'Accepted' THEN 'Not Recovered'
    END) AS "Recovery Status",
    (CASE
        WHEN "Outcome" = '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 "Merchant Credit Amount",
    (CASE
        WHEN "Outcome" = '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 "Merchant Credit Date",
    (CASE
        WHEN "Response Date" IS NOT NULL AND "Merchant Credit Date" IS NOT NULL THEN DATEDIFF(h, "Response Date", "Merchant Credit Date") -- only evaluate credit time if there was a response and a merchant credit
    END) AS "Credit Time"
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 = 'Ethoca' AND e.stage = 'Collaboration Eligibility' AND e.actionname != 'Not Sent' AND e.rn = 1
LEFT JOIN
    recoveryaction AS r2 ON r2.tenantid = d.tenantid AND r2.disputeid = d.disputeid AND r2.stage = 'Collaboration Response' AND r2.recoveryassociation = 'Ethoca'
LEFT JOIN
    collaborationlookup AS m ON m.response = r2.actionname
WHERE
    d.tenantid = 'YOURTENANTID'
    AND d.resolveddatetime =:daterange
ORDER BY
    d.tenantid ASC, d.claimid DESC, d.disputeid DESC)