SELECT
    c.tenantid,
    tenant.name,
    c.clientid,
    client.commonname,
    claim.claimtype,
    claim.reasontype,
    claim.reason,
    (CASE
        WHEN claim.rege45datetime IS NOT NULL THEN 'Reg E'
        WHEN claim.rege90datetime IS NOT NULL THEN 'Reg E'
        WHEN claim.regz90datetime IS NOT NULL THEN 'Reg Z'
        WHEN claim.isfcra = TRUE THEN 'FCRA'
        ELSE 'No Coverage'
    END) AS RegulatoryCoverage,
    claim.channelorigin,
    claim.claimid,
    claim.status,
    c.disputeid,
    dispute.status,
    c.inboundoutbound,
    CAMEL_CASE_TO_STRING(c.channel),
    (CASE
        WHEN c.lettertype IS NULL THEN 'Other'
        ELSE c.lettertype
    END) AS Type,
    c.purpose,
    c.runtimevendor,
    c.pagecount,
    c.isdoublesided,
    c.isreturnmailrequested,
    c.iscolor,
    c.transmissionstatus,
    TO_TIMESTAMP_TZ(c.transmissionstatusdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
    TO_TIMESTAMP_TZ(c.maileddatetime::varchar || ' +0000'),
    (CASE
        WHEN c.createdatetime IS NOT NULL AND c.maileddatetime IS NOT NULL THEN datediff(d, c.createdatetime, c.maileddatetime)
    END) AS DaysToSend,
    (CASE
        WHEN c.createdatetime IS NOT NULL AND c.maileddatetime IS NOT NULL THEN calculate_business_days_udf(c.createdatetime, c.maileddatetime)
    END) AS BusinessDaysToSend,    
    c.executionmethod,
    c.lettersource,
    c.isresend,
    c.correspondenceid
FROM
    correspondence AS c
JOIN tenant AS tenant
    ON tenant.tenantid = c.tenantid
JOIN claim AS claim
    ON claim.tenantid = c.tenantid AND claim.claimid = c.claimid
LEFT JOIN
    dispute AS dispute ON dispute.tenantid = c.tenantid AND dispute.disputeid = c.disputeid
JOIN client AS client
    ON client.tenantid = c.tenantid AND client.clientid = c.clientid
WHERE
    c.channel IS NOT NULL AND c.channel != 'Verbal'