Solved

Ranking data set in sequential order based on value

Posted on 2012-03-27
2
351 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
[X]
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
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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