Ranking data set in sequential order based on value

My client is asking me to produce two sets of pie charts to him based on Month to Date figures and Year to Date figures. What he needs is the Top 10 Insurance Companies based on my "MTDChargeAmt" and "YTDChargeAmt" fields from my query below. My question to the experts is this ... can I add a ranking field to each of these and have it rank the values based on total dollars as to which one has the most equally 1 and count them down to the least sequentially?

SET NOCOUNT ON

DECLARE
    @MTDStartDate DATETIME ,
    @MTDEndDate DATETIME ,
    @YTDStartDate DATETIME ,
    @YTDEndDate DATETIME ,
    @groupby1 VARCHAR(60) ,
    @groupby2 VARCHAR(60) ,
    @groupby3 VARCHAR(60)
	
SET @MTDStartDate = '08/01/2011'
SET @MTDEndDate = '08/31/2011'
SET @YTDStartDate = '01/01/2011'
SET @YTDEndDate = '12/31/2011'
SET @groupby1 = CONVERT(VARCHAR(60) , 'Company')
SET @groupby2 = CONVERT(VARCHAR(60) , 'Insurance Group')
SET @groupby3 = CONVERT(VARCHAR(60) , 'None') ;
WITH    CTE
          AS (
--- Bring in the Charges
               SELECT
                pv.TicketNumber ,
                pvp.DateofServiceFrom ,
                pvp.DateofEntry ,
                pvp.CPTCode ,
                pv.PatientVisitId ,
                pvp.TotalFee AS Charges ,
                pv.DoctorId ,
                d.ListName AS DoctorName ,
                pv.FacilityId ,
                f.ListName AS FacilityName ,
                pv.CompanyId ,
                c.ListName AS CompanyName ,
                ISNULL(ic.ListName , 'Self Pay') AS PrimaryInsuranceCarrier ,
                ISNULL(ig.Name , 'No InsGroup') AS InsuranceGroup ,
                ISNULL(pv.FinancialClassMId , 0) AS FinancialClassMId ,
                ISNULL(fc.Description , 'Unknown') AS FinancialClass
               FROM
                PatientVisit pv
                INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
                INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
                INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
                INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
                LEFT OUTER JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
                LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID
               WHERE
                pvp.TotalFee <> 0
                AND (
                      (
                        '2' = '1'
                        AND pvp.DateofServiceFrom >= ISNULL('01/01/2011' , '1/1/1900')
                        AND pvp.DateofServiceFrom < DATEADD(d , 1 , ISNULL('12/31/2011' , '1/1/3000'))
                      )
                      OR (
                           '2' = '2'
                           AND pvp.DateofEntry >= ISNULL('01/01/2011' , '1/1/1900')
                           AND pvp.DateofEntry < DATEADD(d , 1 , ISNULL('12/31/2011' , '1/1/3000'))
                         )
                    )
                AND  --Filter on Provider
                (
                  (
                    NULL IS NOT NULL
                    AND pv.DoctorID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND  --Filter on Facility
                (
                  (
                    NULL IS NOT NULL
                    AND pv.FacilityID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND  --Filter on Company
                (
                  (
                    NULL IS NOT NULL
                    AND pv.CompanyID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )),
        CTP
          AS (
--- Bring in the Payments	
               SELECT
                pv.TicketNumber ,
                pvp.DateofServiceFrom ,
                pm.DateofEntry ,
                pvp.CPTCode ,
                pv.PatientVisitId ,
                CASE WHEN t.Action = ( 'P' ) THEN td.Amount
                     ELSE 0
                END AS Payments ,
                pv.DoctorId ,
                d.ListName AS DoctorName ,
                pv.FacilityId ,
                f.ListName AS FacilityName ,
                pv.CompanyId ,
                c.ListName AS CompanyName ,
                ISNULL(ic.ListName , 'Self Pay') AS PrimaryInsuranceCarrier ,
                ISNULL(ig.Name , 'No InsGroup') AS InsuranceGroup ,
                ISNULL(pv.FinancialClassMId , 0) AS FinancialClassMId ,
                ISNULL(fc.Description , 'Unknown') AS FinancialClass
               FROM
                PatientVisit pv
                INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
                INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
                INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
                INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
                INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
                INNER JOIN Batch b ON pm.BatchID = b.BatchID
                INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
                LEFT OUTER JOIN TransactionDistributions td ON t.TransactionsId = td.TransactionsId
                LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
                LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
                LEFT OUTER JOIN MedLists dp ON p.DepartmentMID = dp.MedListsID
                LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
                LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
                LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID
                LEFT OUTER JOIN MedLists pt ON ic.PolicyTypeMID = pt.MedListsID
               WHERE
                td.Amount <> 0.00
                AND (
                      (
                        '2' = '1'
                        AND pvp.DateofServiceFrom >= ISNULL('01/01/2011' , '1/1/1900')
                        AND pvp.DateofServiceFrom < DATEADD(d , 1 , ISNULL('12/31/2011' , '1/1/3000'))
                      )
                      OR (
                           '2' = '2'
                           AND pm.DateofEntry >= ISNULL('01/01/2011' , '1/1/1900')
                           AND pm.DateofEntry < DATEADD(d , 1 , ISNULL('12/31/2011' , '1/1/3000'))
                         )
                    )
                AND  --Filter on Provider
                (
                  (
                    NULL IS NOT NULL
                    AND pv.DoctorID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND  --Filter on Facility
                (
                  (
                    NULL IS NOT NULL
                    AND pv.FacilityID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND  --Filter on Company
                (
                  (
                    NULL IS NOT NULL
                    AND pv.CompanyID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )),
        CTA
          AS (
--- Bring in the Adjustments	
               SELECT
                pv.TicketNumber ,
                pvp.DateofServiceFrom ,
                pm.DateofEntry ,
                pvp.CPTCode ,
                pv.PatientVisitId ,
                CASE WHEN t.Action = ( 'A' ) THEN td.Amount
                     ELSE 0
                END AS Adjustments ,
                pv.DoctorId ,
                d.ListName AS DoctorName ,
                pv.FacilityId ,
                f.ListName AS FacilityName ,
                pv.CompanyId ,
                c.ListName AS CompanyName ,
                ISNULL(ic.ListName , 'Self Pay') AS PrimaryInsuranceCarrier ,
                ISNULL(ig.Name , 'No InsGroup') AS InsuranceGroup ,
                ISNULL(pv.FinancialClassMId , 0) AS FinancialClassMId ,
                ISNULL(fc.Description , 'Unknown') AS FinancialClass
               FROM
                PatientVisit pv
                INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
                INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
                INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
                INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
                INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
                INNER JOIN Batch b ON pm.BatchID = b.BatchID
                INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
                LEFT OUTER JOIN TransactionDistributions td ON t.TransactionsId = td.TransactionsId
                LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
                LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
                LEFT OUTER JOIN MedLists dp ON p.DepartmentMID = dp.MedListsID
                LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
                LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID
                LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID
                LEFT OUTER JOIN MedLists pt ON ic.PolicyTypeMID = pt.MedListsID
               WHERE
                td.Amount <> 0.00
                AND (
                      (
                        '2' = '1'
                        AND pvp.DateofServiceFrom >= ISNULL('01/01/2011' , '1/1/1900')
                        AND pvp.DateofServiceFrom < DATEADD(d , 1 , ISNULL('12/31/2011' , '1/1/3000'))
                      )
                      OR (
                           '2' = '2'
                           AND pm.DateofEntry >= ISNULL('01/01/2011' , '1/1/1900')
                           AND pm.DateofEntry < DATEADD(d , 1 , ISNULL('12/31/2011' , '1/1/3000'))
                         )
                    )
                AND  --Filter on Provider
                (
                  (
                    NULL IS NOT NULL
                    AND pv.DoctorID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND  --Filter on Facility
                (
                  (
                    NULL IS NOT NULL
                    AND pv.FacilityID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )
                AND  --Filter on Company
                (
                  (
                    NULL IS NOT NULL
                    AND pv.CompanyID IN ( NULL )
                  )
                  OR ( NULL IS NULL )
                )),
        CTE1
          AS ( SELECT
                CompanyName ,
                DoctorId ,
                DoctorName ,
                FacilityId ,
                FacilityName ,
                PrimaryInsuranceCarrier ,
                InsuranceGroup ,
                CASE WHEN '2' = '1' THEN COALESCE(SUM(CASE WHEN DateofServiceFrom BETWEEN @MTDStartDate AND @MTDEndDate THEN Charges
                                                      END) , 0.00)
                     ELSE COALESCE(SUM(CASE WHEN DateofEntry BETWEEN @MTDStartDate AND @MTDEndDate THEN Charges
                                       END) , 0.00)
                END AS MTDChargeAmt ,
                CASE WHEN '2' = '1' THEN COALESCE(SUM(CASE WHEN DateofServiceFrom BETWEEN @YTDStartDate AND @YTDEndDate THEN Charges
                                                      END) , 0.00)
                     ELSE COALESCE(SUM(CASE WHEN DateofEntry BETWEEN @YTDStartDate AND @YTDEndDate THEN Charges
                                       END) , 0.00)
                END AS YTDChargeAmt ,
                0 AS MTDPaymentAmt ,
                0 AS YTDPaymentAmt ,
                0 AS MTDAdjustmentAmt ,
                0 AS YTDAdjustmentAmt
               FROM
                CTE
               WHERE
                (
                  (
                    '2' = '1'
                    AND DateofServiceFrom BETWEEN @YTDStartDate AND @YTDEndDate
                  )
                  OR (
                       '2' = '2'
                       AND DateofEntry BETWEEN @YTDStartDate AND @YTDEndDate
                     )
                )
               GROUP BY
                CompanyName ,
                FacilityId ,
                FacilityName ,
                PrimaryInsuranceCarrier ,
                InsuranceGroup ,
                DoctorId ,
                DoctorName
               UNION ALL
               SELECT
                CompanyName ,
                DoctorId ,
                DoctorName ,
                FacilityId ,
                FacilityName ,
                PrimaryInsuranceCarrier ,
                InsuranceGroup ,
                0 AS MTDChargeAmt ,
                0 AS YTDChargeAmt ,
                CASE WHEN '2' = '1' THEN COALESCE(SUM(CASE WHEN DateofServiceFrom BETWEEN @MTDStartDate AND @MTDEndDate THEN Payments
                                                      END) , 0.00)
                     ELSE COALESCE(SUM(CASE WHEN DateofEntry BETWEEN @MTDStartDate AND @MTDEndDate THEN Payments
                                       END) , 0.00)
                END AS MTDPaymentAmt ,
                CASE WHEN '2' = '1' THEN COALESCE(SUM(CASE WHEN DateofServiceFrom BETWEEN @YTDStartDate AND @YTDEndDate THEN Payments
                                                      END) , 0.00)
                     ELSE COALESCE(SUM(CASE WHEN DateofEntry BETWEEN @YTDStartDate AND @YTDEndDate THEN Payments
                                       END) , 0.00)
                END AS YTDPaymentAmt ,
                0 AS MTDAdjustmentAmt ,
                0 AS YTDAdjustmentAmt
               FROM
                CTP
               WHERE
                (
                  (
                    '2' = '1'
                    AND DateofServiceFrom BETWEEN @YTDStartDate AND @YTDEndDate
                  )
                  OR (
                       '2' = '2'
                       AND DateofEntry BETWEEN @YTDStartDate AND @YTDEndDate
                     )
                )
               GROUP BY
                CompanyName ,
                FacilityId ,
                FacilityName ,
                PrimaryInsuranceCarrier ,
                InsuranceGroup ,
                DoctorId ,
                DoctorName
               UNION ALL
               SELECT
                CompanyName ,
                DoctorId ,
                DoctorName ,
                FacilityId ,
                FacilityName ,
                PrimaryInsuranceCarrier ,
                InsuranceGroup ,
                0 AS MTDChargeAmt ,
                0 AS YTDChargeAmt ,
                0 AS MTDPaymentAmt ,
                0 AS YTDPaymentAmt ,
                CASE WHEN '2' = '1' THEN COALESCE(SUM(CASE WHEN DateofServiceFrom BETWEEN @MTDStartDate AND @MTDEndDate THEN Adjustments
                                                      END) , 0.00)
                     ELSE COALESCE(SUM(CASE WHEN DateofEntry BETWEEN @MTDStartDate AND @MTDEndDate THEN Adjustments
                                       END) , 0.00)
                END AS MTDAdjustmentAmt ,
                CASE WHEN '2' = '1' THEN COALESCE(SUM(CASE WHEN DateofServiceFrom BETWEEN @YTDStartDate AND @YTDEndDate THEN Adjustments
                                                      END) , 0.00)
                     ELSE COALESCE(SUM(CASE WHEN DateofEntry BETWEEN @YTDStartDate AND @YTDEndDate THEN Adjustments
                                       END) , 0.00)
                END AS YTDAdjustmentAmt
               FROM
                CTA
               WHERE
                (
                  (
                    '2' = '1'
                    AND DateofServiceFrom BETWEEN @YTDStartDate AND @YTDEndDate
                  )
                  OR (
                       '2' = '2'
                       AND DateofEntry BETWEEN @YTDStartDate AND @YTDEndDate
                     )
                )
               GROUP BY
                CompanyName ,
                FacilityId ,
                FacilityName ,
                PrimaryInsuranceCarrier ,
                InsuranceGroup ,
                DoctorId ,
                DoctorName )
    SELECT
        CompanyName ,
        DoctorId ,
        DoctorName ,
        FacilityId ,
        FacilityName ,
        PrimaryInsuranceCarrier ,
        InsuranceGroup ,
        CASE @groupby1
          WHEN 'Company' THEN CompanyName
          WHEN 'Facility' THEN FacilityName
          WHEN 'Insurance Group' THEN InsuranceGroup
          WHEN 'Doctor' THEN DoctorName
          ELSE 'None'
        END AS Group1 ,
        CASE @groupby2
          WHEN 'Company' THEN CompanyName
          WHEN 'Facility' THEN FacilityName
          WHEN 'Insurance Group' THEN InsuranceGroup
          WHEN 'Doctor' THEN DoctorName
          ELSE 'None'
        END AS Group2 ,
        CASE @groupby3
          WHEN 'Company' THEN CompanyName
          WHEN 'Facility' THEN FacilityName
          WHEN 'Insurance Group' THEN InsuranceGroup
          WHEN 'Doctor' THEN DoctorName
          ELSE 'None'
        END AS Group3 ,
        SUM(MTDChargeAmt) AS MTDChargeAmt ,
        SUM(YTDChargeAmt) AS YTDChargeAmt ,
        SUM(MTDPaymentAmt) AS MTDPaymentAmt ,
        SUM(YTDPaymentAmt) AS YTDPaymentAmt ,
        SUM(MTDAdjustmentAmt) AS MTDAdjustmentAmt ,
        SUM(YTDAdjustmentAmt) AS YTDAdjustmentAmt
    FROM
        CTE1
    GROUP BY
        CompanyName ,
        FacilityId ,
        FacilityName ,
        PrimaryInsuranceCarrier ,
        InsuranceGroup ,
        DoctorId ,
        DoctorName

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Maybe something roughly like below.  Add an outer query to your original query and use RANK / ROW_NUMBER to assign the (top) 1 thru 10 number.

You might have to add another subquery level if SQL won't all you to test the RANK() function return value directly.


;WITH CTE...
)

SELECT
        CompanyName ,
        DoctorId ,
        DoctorName ,
        FacilityId ,
        FacilityName ,
        PrimaryInsuranceCarrier ,
        InsuranceGroup ,
        Group1 ,
        Group2 ,
        Group3 ,
        MTDChargeAmt ,
        YTDChargeAmt ,
        MTDPaymentAmt ,
        YTDPaymentAmt ,
        MTDAdjustmentAmt ,
        YTDAdjustmentAmt ,
        RANK() OVER (PARTITION BY CompanyName, FacilityId ORDER BY MTDChargeAMT DESC)
               
FROM (

SELECT
        CompanyName ,
        DoctorId ,
        DoctorName ,
        FacilityId ,
        FacilityName ,
        PrimaryInsuranceCarrier ,
        InsuranceGroup ,
        CASE @groupby1
          WHEN 'Company' THEN CompanyName
          WHEN 'Facility' THEN FacilityName
          WHEN 'Insurance Group' THEN InsuranceGroup
          WHEN 'Doctor' THEN DoctorName
          ELSE 'None'
        END AS Group1 ,
        CASE @groupby2
          WHEN 'Company' THEN CompanyName
          WHEN 'Facility' THEN FacilityName
          WHEN 'Insurance Group' THEN InsuranceGroup
          WHEN 'Doctor' THEN DoctorName
          ELSE 'None'
        END AS Group2 ,
        CASE @groupby3
          WHEN 'Company' THEN CompanyName
          WHEN 'Facility' THEN FacilityName
          WHEN 'Insurance Group' THEN InsuranceGroup
          WHEN 'Doctor' THEN DoctorName
          ELSE 'None'
        END AS Group3 ,
        SUM(MTDChargeAmt) AS MTDChargeAmt ,
        SUM(YTDChargeAmt) AS YTDChargeAmt ,
        SUM(MTDPaymentAmt) AS MTDPaymentAmt ,
        SUM(YTDPaymentAmt) AS YTDPaymentAmt ,
        SUM(MTDAdjustmentAmt) AS MTDAdjustmentAmt ,
        SUM(YTDAdjustmentAmt) AS YTDAdjustmentAmt
    FROM
        CTE1
    GROUP BY
        CompanyName ,
        FacilityId ,
        FacilityName ,
        PrimaryInsuranceCarrier ,
        InsuranceGroup ,
        DoctorId ,
        DoctorName

) AS derived

WHERE
        RANK() OVER (PARTITION BY CompanyName, FacilityId ORDER BY MTDChargeAMT DESC) <= 10
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
Thank you so much for the helping hand, it is deeply appreciated!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.