[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Ranking data set in sequential order based on value

Posted on 2012-03-27
2
Medium Priority
?
359 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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