Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Ranking data set in sequential order based on value

Posted on 2012-03-27
2
350 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:
Scott Pletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL BCP Extra Lines Between Records 2 24
performance query 4 32
Why do I get the message "Message has been thrown by target of an invocation"? 22 53
SQL query and VBA 5 45
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

860 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