Description:	Summary count, total dispute amount, total recovery amount, and total loss amount for disputes resolved within a specified date range.
Sources:	Dispute, Claim

SQL

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

Field Glossary

Field	Definition
Resolved Dispute Count	Posted amount of the transaction. May differ from /wiki/spaces/ARIA/pages/48269484.
Total Disputed Amount	The current amount in dispute, accounting for any recoveries or adjustments.
Disputes with Losses	Count of disputes with a loss (partial or full).
Total Loss Amount	The balance of the Loss accounting collection. Also referred to as LOSSTOTAL.
Disputes with Recovery	Count of disputes with successful recoveries (partial or full).
Cycle 1 Recovery	The total dollar amount of any successful recovery attempts during the first cycle. For card transactions, this refers to the chargeback cycle.
Cycle 2 Recovery	The total dollar amount of any successful recovery attempts during the second cycle. For card transactions, this typically represents the pre-arbitration cycle.
Cycle 3 Recovery	The total dollar amount of any successful recovery attempts during the second cycle. For card transactions, this typically represents an arbitration or compliance cycle.
Total Recovered Amount	Total amount of all successful partial and full recovery attempts. Does not include merchant credits posted directly to the account. Not considered final until the dispute is in a resolved status.
Denied Amount	Total amount of all partial and full denials applied, not including merchant credits.
Recovered Amount	Total amount of all successful partial and full recovery attempts. Does not include merchant credits posted directly to the account. Not considered final until the dispute is in a resolved status.
Disputes With Merchant Credit	Count of disputes with an applied merchant credit (partial or full).
Total Merchant Credits	Amount of all applied merchant credits.
Disputes with Denial	Count of disputes with an applied denial (partial or full).
Total Denied	Total amount of all partial and full denials applied, not including merchant credits.
Recaptured Amount	Represents the total dollar amount of successful loss avoidance, including recoveries, merchant credits, and denials.

Resolved Disputes - Summary

SQL

Field Glossary