Solved

Ranking data set in sequential order based on value

Posted on 2012-03-27
2
341 Views
Last Modified: 2012-03-27
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

0
Comment
Question by:Jeff S
2 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 37771476
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 37771710
Thank you so much for the helping hand, it is deeply appreciated!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now