Resolved Disputes - Summary

WITH disputes AS (
SELECT
d.amount AS "Transaction Amount",
d.disputeamount AS "Dispute Amount",
d.losstotal * -1 AS "Loss Amount",
(CASE WHEN (d.denyreason = 'Merchant Issued Credit' AND (d.disputeamount + d.merchanttotal + d.losstotal) > 0) THEN d.amount + d.merchanttotal + d.losstotal ELSE 0 END) as "Merchant Credit Amount",
d.disputeamount - d.accountholdertotal - "Merchant Credit Amount" 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 "Total 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