I need to get the Top 10 per State and County (1 Viewer)

bconner · Feb 27, 2015

Hello Everyone,
I am trying to get the Top 10 NPI's by State and By County based on the Current_Year_Members_Outreached. Below is the query I created but I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever. My source table only has 67k records which doesn't seem like a lot. So, I have two questions:
1. Does my query below look correct based on what I am trying to achieve?
2. Is there a better way to right this query to get better performance?

As always any help is greatly appreciated....

Code:

SELECT
A.STATE,
A.COUNTY,
A.NPI,
A.[SumOfCurrent Year Members Outreached]
FROM Qry_Provider_Distinct AS A
WHERE A.[SumOfCurrent Year Members Outreached] =
(
SELECT TOP 10
B.[SumOfCurrent Year Members Outreached]
FROM Qry_Provider_Distinct AS B
WHERE A.STATE = B.STATE AND A.COUNTY = B.COUNTY AND A.NPI = B.NPI
ORDER BY
B.[SumOfCurrent Year Members Outreached] DESC
)

jdraw · Feb 27, 2015

Does your query do what you want? Are the results what you expected?
It's easy to test. And it's all read only.
You can always test on a smaller file.

For more info see top N at this link
and this one which deals with speed

bconner · Feb 27, 2015

jdraw,
I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever.

jdraw · Feb 27, 2015

Why not create a copy of the query but limit it with some constraint to give fewer records for testing?
No need to do your testing on the full dataset. Get a test working well, then move to your full data.

Can you post a copy of the database --remove anything private/confidential, then zip?

bconner · Feb 27, 2015

Ok, I have attached a copy of the database. The query I am referring to is named Qry_Provider_Top_10.

jdraw · Feb 27, 2015

Nothing attached.

bconner · Feb 27, 2015

Sorry, here ya go....

jdraw · Feb 27, 2015

bconner,

I really don't understand your data. From my look there seems to be 850+ records in
Qry_Provider_Distinct that have no data in Contracted State???

I do not understand your qry_Provider_Distinct query and all of the aggregate values

Code:

SELECT Provider_Level.State, Provider_Level.County, Provider_Level.NPI
, Sum(Provider_Level.[Current Year Members Outreached]) AS [SumOfCurrent Year Members Outreached]
, Max(Provider_Level.[Address 1]) AS [MaxOfAddress 1]
, Max(Provider_Level.[Address 2]) AS [MaxOfAddress 2], Max(Provider_Level.City) AS MaxOfCity, Max(Provider_Level.State) AS MaxOfState
, Max(Provider_Level.[Zip Code]) AS [MaxOfZip Code]
, Max(Provider_Level.[Phone Number]) AS [MaxOfPhone Number]
FROM Provider_Level
GROUP BY Provider_Level.State, Provider_Level.County, Provider_Level.NPI
ORDER BY Sum(Provider_Level.[Current Year Members Outreached]) DESC;

I need to get the Top 10 per State and County (1 Viewer)

bconner

Registered User.

jdraw

Super Moderator

bconner

Registered User.

jdraw

Super Moderator

bconner

Registered User.

jdraw

Super Moderator

bconner

Registered User.

Attachments

jdraw

Super Moderator

Similar threads

Users who are viewing this thread