WITH disputes AS (
SELECT
    d.amount AS "Transaction Amount",
    d.disputeamount AS "Dispute Amount",
    d.losstotal * -1 AS "Loss Amount",
    (CASE
        WHEN (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid) > 0 THEN (SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid)
        WHEN (d.denyreason = 'Merchant Issued Credit' AND (d.disputeamount + d.merchanttotal + d.losstotal) > 0) THEN d.amountdisputeamount + d.merchanttotal + d.losstotal
        ELSE 0
    END) asAS "Merchant Credit Amount",
    (CASE
        WHEN d.amount = d.disputeamount THEN d.disputeamount - d.accountholdertotal - "Merchant Credit Amount"
        WHEN d.disputeamount = "Merchant Credit Amount" THEN 0
        ELSE d.disputeamount - d.accountholdertotal
    END) AS "Denied Amount",
    (CASE
        WHEN d.merchanttotal * -1 < 0 THEN 0 --account for undue enrichment disputes
        ELSE d.merchanttotal * -1
    END) AS "Recovered Amount",
    IFNULL((SELECT SUM(amount) FROM recoveryaction AS win WHERE win.tenantid = d.tenantid AND win.disputeid = d.disputeid AND (win.actionname IN ('Dispute Accepted - Partial', 'Dispute Accepted - Full', 'No Response Received', 'Representment Not Received', 'Rapid Dispute Resolution') OR win.actionname IN ('Response Received - Full Amount Recovered', 'Response Received - Partial Amount Recovered') AND win.cycle = 1 OR win.actionname IN ('Recovery Pursued', 'Return Submitted') AND win.recoveryassociation = 'ACH') AND win.actioncode != 'UndueEnrichment'), 0) - IFNULL((SELECT SUM(amount) FROM accounting AS a WHERE a.tenantid = d.tenantid AND a.disputeid = d.disputeid AND a.reason = 'Merchant Funds Disbursement' AND a.debitcredit = 'Debit'), 0) AS "Cycle 1 Recovery",
    IFNULL((SELECT SUM(amount) FROM recoveryaction AS win WHERE win.tenantid = d.tenantid AND win.disputeid = d.disputeid AND (win.actionname IN ('Inbound Pre-Arbitration Response Accepted', 'No Inbound Arbitration Received', 'Pre-Arbitration Accepted - Full', 'Pre-Arbitration Accepted - Partial', 'No Pre-Arbitration Response Received') OR win.actionname IN ('Response Received - Full Amount Recovered', 'Response Received - Partial Amount Recovered') AND win.cycle = 2) AND win.actioncode != 'UndueEnrichment'), 0) AS "Cycle 2 Recovery",
    IFNULL((SELECT SUM(amount) FROM recoveryaction AS win WHERE win.tenantid = d.tenantid AND win.disputeid = d.disputeid AND (win.actionname IN ('Arbitration Won', 'Arbitration Split', 'Arbitration Accepted') OR win.actionname IN ('Response Received - Full Amount Recovered', 'Response Received - Partial Amount Recovered') AND win.cycle >= 3) AND win.actioncode != 'UndueEnrichment'), 0) AS "Cycle 3 Recovery",
    "Recovered Amount" + "Merchant Credit Amount" + "Denied Amount" as "Recaptured Amount"    
FROM
    dispute AS d
JOIN
    claim AS c ON c.tenantid = d.tenantid AND c.claimid = d.claimid
LEFT JOIN
    correspondence AS dj ON dj.tenantid = d.tenantid AND dj.disputeid = d.disputeid AND dj.purpose IN ('No Error Occurred', 'Partial Deny No Error', 'Accountholder Liable', 'Resolved Partial Deny No Error') AND dj.runtimememo IS NOT NULL
WHERE
    d.resolveddatetime =:daterange
    AND d.status in ('Resolved-Paid', 'Resolved-Denied') -- exclude open disputes and temporary authorizations that posted as these will have a new dispute record
)
SELECT
    COUNT(*) AS "Resolved Dispute Count",
    SUM("Dispute Amount") AS "TotalAmount Disputed Amount",
    COUNT_IF("Loss Amount" > 0) AS "Disputes With Losses",
    SUM("Loss Amount") AS "Total Loss Amount",
    COUNT_IF("Recovered Amount" > 0) AS "Disputes With Recovery",
    SUM("Cycle 1 Recovery") AS "Cycle 1 Recovered Amount",
    SUM("Cycle 2 Recovery") AS "Cycle 2 Recovered Amount",
    SUM("Cycle 3 Recovery") AS "Cycle 3 Recovered Amount",
    SUM("Recovered Amount") AS "Total Recovered Amount",
    COUNT_IF("Merchant Credit Amount" > 0) AS "Disputes With Merchant Credit",
    SUM("Merchant Credit Amount") AS "Total Merchant Credits",
    COUNT_IF("Denied Amount" > 0) AS "Disputes With Denial",
    SUM("Denied Amount") AS "Total Denied",
    SUM("Recaptured Amount") AS "Recaptured Amount" 
FROM
disputes     
 disputes