New Patients in Date Range

/*802 List of new patients in a date range, with their referral source and their overall net production*/
/*Also shows referral source summaries at the bottom with # of patients referred, and total net production of the patients they have referred*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 04/16/2018*/

/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET
    @FromDate = '2018-10-01',
    @ToDate = '2018-10-31'
;


/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SELECT A.`Pat#`,
A.PatientName AS 'Patient Name',
A.ReferralSource AS 'Referral Source',
A.`#Referred` AS '# Referred',
A.Fees,
A.Adjustments AS 'Adjusts',
A.Writeoffs,
A.NetProduction AS 'Net Production'
FROM (
    SELECT 1 AS ItemOrder,
    patresult.PatNum AS 'Pat#',
    CONCAT(patresult.LName ,', ',patresult.FName) AS 'PatientName',
    patresult.ReferralSources AS 'ReferralSource',
    '' AS '#Referred',
    FORMAT(procfees.TotalFees,2) AS 'Fees',
    FORMAT(COALESCE(adjusts.TotalAdjusts,0),2) AS 'Adjustments',
    FORMAT(-procfees.Writeoffs,2) AS 'Writeoffs',
    FORMAT(procfees.TotalFees+COALESCE(adjusts.TotalAdjusts,0)-procfees.Writeoffs,2) AS 'NetProduction'
    FROM (
        SELECT p.PatNum,p.LName,p.FName,ref.ReferralSources
        FROM patient p
        INNER JOIN (
            SELECT ra.PatNum,GROUP_CONCAT(DISTINCT r.LName,', ',r.FName ORDER BY r.LName,r.FName SEPARATOR ' | ') AS ReferralSources
            FROM refattach ra
            INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
            WHERE ra.RefType = 1 /*RefFrom*/
            GROUP BY ra.PatNum
        ) ref ON ref.PatNum=p.PatNum
        INNER JOIN (
            SELECT pl.PatNum,
            MIN(pl.ProcDate) AS FirstVisitDate
            FROM procedurelog pl
            WHERE pl.ProcStatus=2 /*Complete*/
            GROUP BY pl.PatNum
        ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
        GROUP BY p.PatNum
    ) patresult
    INNER JOIN (
        SELECT A.PatNum,SUM(A.TotalFees) AS TotalFees,SUM(A.Writeoffs) AS Writeoffs
        FROM (
            SELECT pl.PatNum,
            pl.ProcFee * (pl.UnitQty + pl.BaseUnits) AS TotalFees,
            COALESCE(SUM((CASE
                WHEN cp.Status IN (1, 4)
                /*Received,Supplemental*/
                    THEN cp.Writeoff
                WHEN cp.Status IN (0, 6)
                /*NotReceived,Estimate*/
                    THEN ( CASE
                        WHEN cp.WriteOffEstOverride != - 1
                            THEN cp.WriteOffEstOverride
                        ELSE (
                            CASE
                            WHEN cp.WriteoffEst != - 1
                                THEN cp.WriteoffEst
                            ELSE 0
                        END)
                    END)
                END)
            ), 0) AS WriteOffs
            FROM procedurelog pl
            INNER JOIN (
                SELECT p.PatNum
                FROM patient p
                INNER JOIN (
                    SELECT ra.PatNum
                    FROM refattach ra
                    INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                    WHERE ra.RefType = 1 /*RefFrom*/
                    GROUP BY ra.PatNum
                ) ref ON ref.PatNum=p.PatNum
                INNER JOIN (
                    SELECT pl.PatNum,
                    MIN(pl.ProcDate) AS FirstVisitDate
                    FROM procedurelog pl
                    WHERE pl.ProcStatus=2 /*Complete*/
                    GROUP BY pl.PatNum
                ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
                GROUP BY p.PatNum
            ) AS presult ON pl.PatNum=presult.PatNum
            LEFT JOIN claimproc cp ON cp.ProcNum = pl.ProcNum AND cp.Status IN (0, 1, 4, 6)
            WHERE pl.ProcStatus=2 /*Complete*/
            GROUP BY pl.ProcNum
        ) A
        GROUP BY A.PatNum
    ) procfees ON procfees.PatNum=patresult.PatNum
    LEFT JOIN (
        SELECT adj.PatNum, SUM(adj.AdjAmt) AS TotalAdjusts
        FROM adjustment adj
        INNER JOIN (SELECT p.PatNum
            FROM patient p
            INNER JOIN (
                SELECT ra.PatNum
                FROM refattach ra
                INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                WHERE ra.RefType = 1 /*RefFrom*/
                GROUP BY ra.PatNum
            ) ref ON ref.PatNum=p.PatNum
            INNER JOIN (
                SELECT pl.PatNum,
                MIN(pl.ProcDate) AS FirstVisitDate
                FROM procedurelog pl
                WHERE pl.ProcStatus=2 /*Complete*/
                GROUP BY pl.PatNum
            ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
            GROUP BY p.PatNum) AS presult ON adj.PatNum=presult.PatNum
        GROUP BY adj.PatNum
    ) adjusts ON adjusts.PatNum=patresult.PatNum
    GROUP BY patresult.PatNum

    UNION ALL

    SELECT 2 AS ItemOrder,
    '--------' AS 'Pat#',
    '--------' AS 'PatientName',
    '--------' AS 'ReferralSource',
    '--------' AS '#Referred',
    '--------' AS 'Fees',
    '--------' AS 'Adjustments',
    '--------' AS 'Writeoffs',
    '--------' AS 'NetProduction'

    UNION ALL

    SELECT 3 AS ItemOrder,
    '' AS 'Pat#',
    '' AS 'PatientName',
    'Total of Above Patients:' AS 'ReferralSource',
    COUNT(C.`Pat#`) AS '#Referred',
    FORMAT(SUM(C.Fees),2) AS 'Fees',
    FORMAT(SUM(C.Adjustments),2) AS 'Adjustments',
    FORMAT(-SUM(C.Writeoffs),2) AS 'Writeoffs',
    FORMAT(SUM(C.NetProduction),2) AS 'NetProduction'
    FROM (
        SELECT patresult.PatNum AS 'Pat#',
        procfees.TotalFees AS 'Fees',
        COALESCE(adjusts.TotalAdjusts,0) AS 'Adjustments',
        procfees.Writeoffs AS 'Writeoffs',
        procfees.TotalFees+COALESCE(adjusts.TotalAdjusts,0)-procfees.Writeoffs AS 'NetProduction'
        FROM (
            SELECT p.PatNum,p.LName,p.FName,ref.ReferralSources
            FROM patient p
            INNER JOIN (
                SELECT ra.PatNum,GROUP_CONCAT(DISTINCT r.LName,', ',r.FName ORDER BY r.LName,r.FName SEPARATOR ' | ') AS ReferralSources
                FROM refattach ra
                INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                WHERE ra.RefType = 1 /*RefFrom*/
                GROUP BY ra.PatNum
            ) ref ON ref.PatNum=p.PatNum
            INNER JOIN (
                SELECT pl.PatNum,
                MIN(pl.ProcDate) AS FirstVisitDate
                FROM procedurelog pl
                WHERE pl.ProcStatus=2 /*Complete*/
                GROUP BY pl.PatNum
            ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
            GROUP BY p.PatNum
        ) patresult
        INNER JOIN (
            SELECT A.PatNum,SUM(A.TotalFees) AS TotalFees,SUM(A.Writeoffs) AS Writeoffs
            FROM (
                SELECT pl.PatNum,
                pl.ProcFee * (pl.UnitQty + pl.BaseUnits) AS TotalFees,
                COALESCE(SUM((CASE
                    WHEN cp.Status IN (1, 4)
                    /*Received,Supplemental*/
                        THEN cp.Writeoff
                    WHEN cp.Status IN (0, 6)
                    /*NotReceived,Estimate*/
                        THEN ( CASE
                            WHEN cp.WriteOffEstOverride != - 1
                                THEN cp.WriteOffEstOverride
                            ELSE (
                                CASE
                                WHEN cp.WriteoffEst != - 1
                                    THEN cp.WriteoffEst
                                ELSE 0
                            END)
                        END)
                    END)
                ), 0) AS WriteOffs
                FROM procedurelog pl
                INNER JOIN (
                    SELECT p.PatNum
                    FROM patient p
                    INNER JOIN (
                        SELECT ra.PatNum
                        FROM refattach ra
                        INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                        WHERE ra.RefType = 1 /*RefFrom*/
                        GROUP BY ra.PatNum
                    ) ref ON ref.PatNum=p.PatNum
                    INNER JOIN (
                        SELECT pl.PatNum,
                        MIN(pl.ProcDate) AS FirstVisitDate
                        FROM procedurelog pl
                        WHERE pl.ProcStatus=2 /*Complete*/
                        GROUP BY pl.PatNum
                    ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
                    GROUP BY p.PatNum
                ) AS presult ON pl.PatNum=presult.PatNum
                LEFT JOIN claimproc cp ON cp.ProcNum = pl.ProcNum AND cp.Status IN (0, 1, 4, 6)
                WHERE pl.ProcStatus=2 /*Complete*/
                GROUP BY pl.ProcNum
            ) A
            GROUP BY A.PatNum
        ) procfees ON procfees.PatNum=patresult.PatNum
        LEFT JOIN (
            SELECT adj.PatNum, SUM(adj.AdjAmt) AS TotalAdjusts
            FROM adjustment adj
            INNER JOIN (SELECT p.PatNum
                FROM patient p
                INNER JOIN (
                    SELECT ra.PatNum
                    FROM refattach ra
                    INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                    WHERE ra.RefType = 1 /*RefFrom*/
                    GROUP BY ra.PatNum
                ) ref ON ref.PatNum=p.PatNum
                INNER JOIN (
                    SELECT pl.PatNum,
                    MIN(pl.ProcDate) AS FirstVisitDate
                    FROM procedurelog pl
                    WHERE pl.ProcStatus=2 /*Complete*/
                    GROUP BY pl.PatNum
                ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
                GROUP BY p.PatNum) AS presult ON adj.PatNum=presult.PatNum
            GROUP BY adj.PatNum
        ) adjusts ON adjusts.PatNum=patresult.PatNum
        GROUP BY patresult.PatNum
    ) C

    UNION ALL

    SELECT 4 AS ItemOrder,
    '--------' AS 'Pat#',
    '--------' AS 'PatientName',
    '--------' AS 'ReferralSource',
    '--------' AS '#Referred',
    '--------' AS 'Fees',
    '--------' AS 'Adjustments',
    '--------' AS 'Writeoffs',
    '--------' AS 'NetProduction'

    UNION ALL

    SELECT 5 AS ItemOrder,
    '' AS 'Pat#',
    '' AS 'PatientName',
    CONCAT(ref.LName,', ',ref.FName) AS 'ReferralSource',
    COUNT(DISTINCT ref.PatNum) AS '#Referred',
    FORMAT(SUM(procfees.TotalFees),2) AS 'Fees',
    FORMAT(COALESCE(SUM(adjusts.TotalAdjusts),0),2) AS 'Adjustments',
    FORMAT(-SUM(procfees.Writeoffs),2) AS 'Writeoffs',
    FORMAT(SUM(procfees.TotalFees)+COALESCE(SUM(adjusts.TotalAdjusts),0)-SUM(procfees.Writeoffs),2) AS 'NetProduction'
    FROM (
        SELECT DISTINCT p.PatNum,ref.LName,ref.FName,ref.ReferralNum
        FROM patient p
        INNER JOIN (
            SELECT ra.PatNum,r.LName,r.FName,r.ReferralNum
            FROM refattach ra
            INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
            WHERE ra.RefType = 1 /*RefFrom*/
        ) ref ON ref.PatNum=p.PatNum
        INNER JOIN (
            SELECT pl.PatNum,
            MIN(pl.ProcDate) AS FirstVisitDate
            FROM procedurelog pl
            WHERE pl.ProcStatus=2 /*Complete*/
            GROUP BY pl.PatNum
        ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
    ) ref
    INNER JOIN (
        SELECT A.PatNum, SUM(A.TotalFees) AS TotalFees, SUM(A.Writeoffs) AS Writeoffs
        FROM (
            SELECT pl.PatNum,
            pl.ProcFee * (pl.UnitQty + pl.BaseUnits) AS TotalFees,
            COALESCE(SUM((CASE
                WHEN cp.Status IN (1, 4)
                /*Received,Supplemental*/
                    THEN cp.Writeoff
                WHEN cp.Status IN (0, 6)
                /*NotReceived,Estimate*/
                    THEN ( CASE
                        WHEN cp.WriteOffEstOverride != - 1
                            THEN cp.WriteOffEstOverride
                        ELSE (
                            CASE
                            WHEN cp.WriteoffEst != - 1
                                THEN cp.WriteoffEst
                            ELSE 0
                        END)
                    END)
                END)
            ), 0) AS WriteOffs
            FROM procedurelog pl
            INNER JOIN (SELECT p.PatNum
                FROM patient p
                INNER JOIN (
                    SELECT ra.PatNum
                    FROM refattach ra
                    INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                    WHERE ra.RefType = 1 /*RefFrom*/
                    GROUP BY ra.PatNum
                ) ref ON ref.PatNum=p.PatNum
                INNER JOIN (
                    SELECT pl.PatNum,
                    MIN(pl.ProcDate) AS FirstVisitDate
                    FROM procedurelog pl
                    WHERE pl.ProcStatus=2 /*Complete*/
                    GROUP BY pl.PatNum
                ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
                GROUP BY p.PatNum
            ) AS presult ON pl.PatNum=presult.PatNum
            LEFT JOIN claimproc cp ON cp.ProcNum = pl.ProcNum AND cp.Status IN (0, 1, 4, 6)
            WHERE pl.ProcStatus=2 /*Complete*/
            GROUP BY pl.ProcNum
        ) A
        GROUP BY A.PatNum
    ) procfees ON procfees.PatNum=ref.PatNum
    LEFT JOIN (
        SELECT adj.PatNum, SUM(adj.AdjAmt) AS TotalAdjusts
        FROM adjustment adj
        INNER JOIN (SELECT p.PatNum
            FROM patient p
            INNER JOIN (
                SELECT ra.PatNum
                FROM refattach ra
                INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
                WHERE ra.RefType = 1 /*RefFrom*/
                GROUP BY ra.PatNum
            ) ref ON ref.PatNum=p.PatNum
            INNER JOIN (
                SELECT pl.PatNum,
                MIN(pl.ProcDate) AS FirstVisitDate
                FROM procedurelog pl
                WHERE pl.ProcStatus=2 /*Complete*/
                GROUP BY pl.PatNum
            ) newpatsfilter ON newpatsfilter.PatNum=p.PatNum AND newpatsfilter.FirstVisitDate BETWEEN @FromDate AND @ToDate
            GROUP BY p.PatNum
        ) AS presult ON adj.PatNum=presult.PatNum
        GROUP BY adj.PatNum
    ) adjusts ON adjusts.PatNum=ref.PatNum
    GROUP BY ref.ReferralNum
) A
ORDER BY ItemOrder,A.PatientName,A.`#Referred` DESC,A.NetProduction DESC