Production and Patients by Carrier, previous 18 months.

-- Production and Patients by Carrier. Provided by Divergent Dental Resources, LLC. Contact info@divergentdental.com for more.
SET
    @FromDate = CURDATE() - INTERVAL 18 MONTH,
    @ToDate = CURDATE(),
    @Carrier = '%%' -- Change 'Delta%' to '%%' to pull all patients.
;
SELECT
    CASE WHEN c.CarrierName IS NULL THEN '*Self-Pay' ELSE SUBSTRING_INDEX(c.CarrierName, ' ', 2) END AS PriCarrier,
    COUNT(DISTINCT t1.PatNum) AS TotPats,
    ROUND(SUM(CASE WHEN t1.Type LIKE 'Fee' THEN t1.Amt ELSE 0 END), 2) AS $Gross,
    ROUND(SUM(CASE WHEN t1.Type LIKE 'WO' THEN t1.Amt ELSE 0 END), 2) AS $WO,
    ROUND(SUM(CASE WHEN t1.Type LIKE 'Fee' THEN t1.Amt ELSE 0 END), 2) - ROUND(SUM(CASE WHEN t1.Type LIKE 'WO' THEN t1.Amt ELSE 0 END), 2) AS $Net,
    ROUND((SUM(CASE WHEN t1.Type LIKE 'Fee' THEN t1.Amt ELSE 0 END) - SUM(CASE WHEN t1.Type LIKE 'WO' THEN t1.Amt ELSE 0 END)) / COUNT(DISTINCT t1.PatNum), 2) AS $NetPerPat
FROM patient p
INNER JOIN
    (
    SELECT
        pl.PatNum,
        pl.ProcNum,
        'Fee' AS `Type`,
        pl.ProcFee AS Amt
    FROM procedurelog pl
    WHERE
        pl.ProcDate BETWEEN @FromDate AND @ToDate
        AND pl.ProcStatus = 2

    UNION ALL

    SELECT
        cp.PatNum,
        cp.ProcNum,
        'WO' AS `Type`,
        cp.WriteOff
    FROM claimproc cp
    WHERE
        cp.Status IN (1,4,0) /*received or supplemental or notreceived*/
        AND cp.ProcDate BETWEEN @FromDate AND @ToDate
    ) t1 ON p.PatNum = t1.PatNum
LEFT JOIN patplan pp ON pp.PatNum = p.PatNum AND pp.Ordinal = 1 -- Primary Insurance
LEFT JOIN inssub ib ON pp.InsSubNum = ib.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum = ib.PlanNum
LEFT JOIN carrier c ON c.CarrierNum = ip.CarrierNum AND c.CarrierName LIKE @Carrier
WHERE
    p.PatStatus = 0 -- Active Patients
GROUP BY PriCarrier
ORDER BY $Net DESC
;