SELECT --top 2500
    d.tenantid,
    t.name,
    d.clientid,
    cl.commonname,
    c.claimtype,
    c.reasontype,
    CAMEL_CASE_TO_STRING(c.reason) AS "REASON",
    (CASE
        WHEN c.rege45datetime IS NOT NULL THEN 'Reg E'
        WHEN c.rege90datetime IS NOT NULL THEN 'Reg E'
        WHEN c.regz90datetime IS NOT NULL THEN 'Reg Z'
        WHEN c.isfcra = TRUE THEN 'FCRA'
        ELSE 'No Coverage'
    END) AS "Regulatory RegulatoryCoverageCoverage",
    d.acquirernetwork,(CASE
    c.channelorigin,    WHEN cd.cardtype,acquirernetwork = 'MasterCard' THEN  c.cardbin,'Mastercard'
     d.transactiontype,    WHEN d.claimid,acquirernetwork = 'Debit Mastercard'  d.disputeid,THEN 'Mastercard Debit'
    cs.state,     CAMEL_CASE_TO_STRING(WHEN d.status),acquirernetwork IS NULL THEN 'Unknown'
(CASE         WHENELSE d.status LIKE CONCAT('%','-','%') THEN SUBSTRING(d.status, 1, CHARINDEX('-', d.status)-1        )
        ELSE d.status
    END) as statustypeacquirernetwork
    END) AS "Network",
    c.channelorigin,
    c.cardtype,
    c.cardbin,
    d.claimid,
    d.disputeid,
    c.status,
    d.status,
    d.decision,
    CAMEL_CASE_TO_STRING(d.denyreason) AS "DenyReason",
    c.customercontactdate,
    TO_TIMESTAMP_TZ(d.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.investigationcompletedatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(d.resolveddatetime::varchar || ' +0000'),
    (CASE
        WHEN d.status = 'Resolved-Paid' THEN 'Paid'
        WHEN d.status = 'Resolved-Denied' THEN 'Denied'
        WHEN d.status = 'Resolved-NoPosting' THEN 'Never Posted'
    END) as "Resolution",
    DATEDIFF(d, d.createdatetime, d.resolveddatetime) AS "ResolutionTimeDays",
    DATEDIFF(d, c.customercontactdate, to_date(d.investigationcompletedatetime)) AS CustomerResolutionTimeDays,
    d.authorizationstatus,
    d.merchantcategorycode,
    d.merchantname,
    d.decisionposentrymode,
    CAMEL_CASE_TO_STRING(d.denyreason) AS DenyReasondescription,
    d.transactiondatedisputeamount,
    cd.customercontactdateamount,
    d.createdatetimeaccountholdertotal,
    to_date(d.investigationcompletedatetime) AS InvestigationDate,
    to_date(d.resolveddatetime) AS ResolvedDate,
    d.authorizationstatus IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = d.tenantid AND mc.disputeid = d.disputeid), 0) AS "MerchantCreditAmount",
    d.merchantcategorycode,IFNULL((CASE
    d.merchantname,    WHEN d.posentrymode,
    d.description,
    d.disputeamount,
    d.amount,
    d.accountholdertotal,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mctransactiontype = 'ACH' THEN (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND r.disputeid = d.disputeid AND r.actionname = 'Recovery Pursued') - (SELECT COUNT(*) FROM recoveryaction as r WHERE r.tenantid = d.tenantid AND mc.disputeid = d.disputeid), 0) AS MerchantCreditAmount, r.disputeid = d.disputeid AND r.actionname = 'Recovery Cancelled')
        ELSE (CASE
        WHENSELECT max(r.cycle) FROM recoveryaction as r WHERE r.tenantid = d.statustenantid ILIKE '%resolved%' THENAND r.disputeid = d.merchanttotal * -1disputeid)
    END), 0) AS "Recovery Attempts",
ELSE 0   d.merchanttotal * END)-1 AS "RecoveredAmount",
    d.losstotal * -1 AS "LossAmount",
    (CASEd.disputeamount - d.accountholdertotal - "MerchantCreditAmount" AS "DeniedAmount",
  WHEN d.status ILIKE '%resolved%' AND d.originaldisputeamount IS NOT NULL THEN d.originaldisputeamount - d.accountholdertotal - MerchantCreditAmount "MerchantCreditAmount" + "RecoveredAmount" + "DeniedAmount" AS "RecapturedAmount",
    (CASE
        WHEN d.statusisreopened ILIKE= '%resolved%'TRUE THEN d.disputeamount - d.accountholdertotal - MerchantCreditAmountTRUE
         ELSE 0FALSE
    END) AS DeniedAmount,
    d.isreopenedAS "Reopened"
FROM dispute AS d
JOIN tenant AS t
    onON d.tenantid = t.tenantid
JOIN claim AS c
    onON d.tenantid = c.tenantid andAND d.claimid = c.claimid
JOIN client AS cl
    onON d.tenantid = cl.tenantid andAND d.clientid = cl.clientid
LEFTWHERE
JOIN claim_state AS cs ON csd.status = c.status IN ('Resolved-Paid', 'Resolved-Denied', 'Resolved-NoPosting')