SQL Percent Of Total Records

Hello experts,

I have a SQL query here, one column is the place_of_service column that may or may not have a value of 11 in it.  I want to find the percentage of rows by provider_id 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 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.

PortletPaulfreelancerCommented:
something like this?
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
      , count(case when s.place_of_service = 11 then 1 else null end) * 100
        / count(s.place_of_service) 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 c.practice_id = '0002'
        AND pe.enc_timestamp BETWEEN @start_date AND @end_date
GROUP BY
        pe.rendering_provider_id
HAVING
        count(case when s.place_of_service = 11 then 1 else null end) * 100
        / count(s.place_of_service) > 10

Open in new window

0
PortletPaulfreelancerCommented:
or, if also wanting other columns, perhaps this?
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()

;WITH 
  D AS (
        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 c.practice_id = '0002'
                AND pe.enc_timestamp BETWEEN @start_date AND @end_date
        )
, P AS (            
        SELECT
                rendering_provider_id
              , count(case when place_of_service = 11 then 1 else null end) * 100
                / count(place_of_service) as percentofrows
        FROM D
        GROUP BY
                pe.rendering_provider_id
        HAVING
                count(case when place_of_service = 11 then 1 else null end) * 100
                / count(place_of_service) > 10
       )
SELECT
*
FROM D
INNER JOIN P
        ON D.rendering_provider_id = p.rendering_provider_id
;

Open in new window

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
robthomas09Author Commented:
Thanks!
0
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.