asked on

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!

PortletPaul

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robthomas09

ASKER

Thanks!