About
If your providers practice at multiple locations, then calculate the total volume per location. This SQL code is used to calculate the total volume per location metric. This SQL looks at the location breakdown of billed encounters by rendering provider, for the period under review. You will be prompted for a date range. Set the date range here for your 90 day or 365 day MU reporting period. The results will report on all encounters in all places of service and all physical locations where billing took place in OP.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This should be used when determining Meaningful Use Volume Counts using the OP Software. See Introduction: Determining Medicaid Volume using the OP Software.
- The OP software has no way of knowing patient billed volumes billed outside of OP. You will have to query other systems at those locations to determine volume for those locations.
- It is not uncommon to have a few rows where a location was not assigned or a provider was not assigned.
Code
To highlight and copy the code below to your clipboard, simply click the Copy button.
select staffname, count(uniqid) as encounters, pos, loc
from
(
select a.* , location.loc_name as practice_location, labname as facility_name, staffname, case when pos = 11 then location.loc_name else labname end as loc
from (
select loc_id, pos, L, rend_addr_id, uniqid from
(
select distinct loc_id, pos, rend_addr_id, L, uniqid from (
select loc_id, line32otherfacility as L, pos, rend_addr_id, patno, date1, (patno || ' ' || date1) as uniqid, cptcode from archive_transactions at1
where at1.cptcode not in ('1','2','3','4') and pos not in (21,23) and
at1.date1 between :attestation_start and :attestation_end and at1.archive_flag = 1
) ) ) a
left outer join location on location.id = a.loc_id
left outer join staff1 on staff1.staffid = a.rend_addr_id
left outer join laboratory on laboratory.labinit = a.L
) group by staffname, pos, loc
