SQL - percentage of total - query syntax help

Hello experts,

I have a SQL query here that will return me a nice set of rows, and one column is the place_of_service column that may or may not have an 11 in it.  I want to find the percentage of rows by provider and find out if any single provider has more than 10% of the records with 11 as the place_of_service.  

My query:

declare @start_date datetime
declare @end_date datetime
declare @provider_id varchar(36)

set @start_date = '2000-01-01 00:00:16.393'
set @end_date = getdate()

select pe.rendering_provider_id, pe.enc_timestamp, c.cpt4_code_id, pe.person_id, s.place_of_service
from charges c
join patient_encounter pe
on pe.person_id = c.person_id
and pe.enc_id = c.source_id
join service_item_mstr s
on s.service_item_id = c.cpt4_code_id
where c.cpt4_code_id in ('92002','92004','92012','92014','99201','99202','99203','99204','99205',
                         '99212','99213','99214','99215','99304','99305','99306','99307','99308',
                         '99309','99310','99324','99325','99326','99327','99328','99334','99335',
                         '99336','99337')
and s.place_of_service = '11'
and c.practice_id = '0002'
and pe.enc_timestamp between @start_date and @end_date

Open in new window


maybe something like:

WITH AVER 
     AS (SELECT pe.rendering_provider_id, pe.enc_timestamp, c.cpt4_code_id, pe.person_id, s.place_of_service
                ( s.place_of_service * 100 ) / SUM(s.place_of_service) OVER (PARTITION BY 1) 
                AS 
                percentofrows
         FROM   charges c
join patient_encounter pe
on pe.person_id = c.person_id
and pe.enc_id = c.source_id
join service_item_mstr s
on s.service_item_id = c.cpt4_code_id
where c.cpt4_code_id in ('92002','92004','92012','92014','99201','99202','99203','99204','99205',
                         '99212','99213','99214','99215','99304','99305','99306','99307','99308',
                         '99309','99310','99324','99325','99326','99327','99328','99334','99335',
                         '99336','99337')
and s.place_of_service = '11'
and c.practice_id = '0002'
and pe.enc_timestamp between @start_date and @end_date) 

SELECT * 
FROM   AVER
WHERE  percentofrows >= 10 
ORDER BY pe.rendering_provider_id desc

Open in new window


Thoughts?

Thanks!
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sameer2010Commented:
The query would always return rows with place_of_service=11 only.

Do you want to change original query as well?
0
robthomas09Author Commented:
whoops yeah I just had that in there for testing purposes.  It will be changed in the final version to remove that and s.place_of_service = '11' from the main query.
0
Luke ChungPresidentCommented:
See my Microsoft Access Query Tips and Techniques paper's section on Percent of Total: http://www.fmsinc.com/MicrosoftAccess/query/index.html#Percent_of_Total

A sample database is also included with the paper. Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.