Resolved Disputes - Summary

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.disputeamount + d.merchanttotal + d.losstotal) > 0) as "Disputes With Merchant Credit",
    SUM(CASE WHEN (d.denyreason = 'Merchant Issued Credit' AND (d.disputeamount + d.merchanttotal + d.losstotal) > 0) THEN d.disputeamount + 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