x
Solved

# Ranking data set in sequential order based on value

Posted on 2012-03-27
Medium Priority
357 Views
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 (
SELECT
pv.TicketNumber ,
pvp.DateofServiceFrom ,
pm.DateofEntry ,
pvp.CPTCode ,
pv.PatientVisitId ,
CASE WHEN t.Action = ( 'A' ) THEN td.Amount
ELSE 0
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 ,
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 ,
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)
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)
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 ,
FROM
CTE1
GROUP BY
CompanyName ,
FacilityId ,
FacilityName ,
PrimaryInsuranceCarrier ,
InsuranceGroup ,
DoctorId ,
DoctorName
``````
0
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

LVL 70

Accepted Solution

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 ,
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 ,
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

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

## Featured Post

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
###### Suggested Courses
Course of the Month15 days, 7 hours left to enroll