Avatar of biotec
biotec
 asked on

SQL query top 10 orders per provider

I have a query below that gives me top diagnoses (orders) but can't seem to get it per provider_id. I need top 10 diagnoses done for each provider so group by provider and show each providers' top 10 for the year. Any help is appreciated. Thanks

select TOP 10 e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
--and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
ORDER BY COUNT(icd9cm_code_id) DESC

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
biotec

8/22/2022 - Mon
SOLUTION
Surendra Nath

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
biotec

ASKER
What I am trying to get is each providers top 10 diagnosis/orders so each provider should show 10 or close to it,  diagnosis codes and how many times they ordered each of those during the year. I would expect to see their provider_id 10 times in a row with each diagnosis codes being used less than the one above it.
An example is below, if I use this I get one provider and all their top 10 diagnoses codes in order. All I'm trying to do is get this same thing but for all providers. The code below with a provider_id added gives the results shown on the attached Excel spreadsheet.
Your query was close but did not seem to return anything that gave a per provider top 10. Thanks

select TOP 10 e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
ORDER BY COUNT(icd9cm_code_id) DESC

Open in new window

diagnosis_per_provider.xlsx
Surendra Nath

Missed out one thing in my query try the below one

with cte as (
select e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
--and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
),cte2 as (
Select row_number() over(partition by provider_id order by times_used desc) as rn,* from cte)
Select * from cte2 where rn<=10

Open in new window

biotec

ASKER
Unfortunately this really didn't change anything. The format I need is in the example spreadsheet I attached. It needs to be broken down by provider_id and should return 10 rows per provider_id. The query I added does just that, but unfortunately I can only figure out how to do it for one provider at a time. thanks
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
awking00

What happens if you change this -
Select row_number() over(partition by provider_id order by times_used desc) as rn,* from cte)
to this -
Select row_number() over(partition by provider_id, icd90cm_code_id order by times_used desc) as rn,* from cte)
biotec

ASKER
Closer but that didn't restrict it to top 10 per provider_id I got 24 in some cases. Thanks
awking00

Can you post the script that didn't restrict the output to 10 records per provider?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
biotec

ASKER
I need it to order by provider_id then by times used if possible.
with cte as (
select e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
--and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
),cte2 as (
Select row_number() over(partition by provider_id, icd9cm_code_id order by times_used desc) as rn,* from cte)
Select * from cte2 where rn<=10

Open in new window

ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
biotec

ASKER
Thanks
biotec

ASKER
Thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61