SELECT
COUNT(*) as "Resolved Disputes",
SUM(d.amount) as "Total Amount",
COUNT_IF(d.losstotal < 0) as "Disputes With Losses",
SUM(d.losstotal * -1) as "Total Loss Amount",
COUNT_IF(d.merchanttotal < 0) as "Disputes With Recovery",
SUM(d.merchanttotal * -1) as "Total Recovered Amount",
COUNT_IF(d.denyreason = 'Merchant Issued Credit' AND (d.amountdisputeamount + d.merchanttotal + d.losstotal) > 0) as "Disputes With Merchant Credit",
SUM(CASE WHEN (d.denyreason = 'Merchant Issued Credit' AND (d.amountdisputeamount + d.merchanttotal + d.losstotal) > 0) THEN d.amountdisputeamount + d.merchanttotal + d.losstotal ELSE 0 END) as "Total Merchant Credits",
SUM(CASE WHEN (d.denyreason = 'Merchant Issued Credit' AND (d.disputeamount + d.merchanttotal + d.losstotal) > 0) THEN d.disputeamount - d.accountholdertotal - (d.disputeamount + d.merchanttotal + d.losstotal) ELSE d.disputeamount - d.accountholdertotal END) as "Total Denied",
FROM
dispute AS d
JOIN
claim AS c ON c.tenantid = d.tenantid AND c.claimid = d.claimid
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
|