Number of Services for Patients by Service Code and Rendering Provider

Here is the information I need:
Clients name: (using the Intake as of Sept. 2003)
Providers name:
Number of times in Individual sessions (CPT 90806 Code: IP)
Number of times in Group (CPT 90853 Code: Grp)
Is that possible to get the clients name and the number of sessions they have been in for individual and
group sessions?

I took a little liberty with your request to show the number of sessions of all rendered services, but this
can be trimmed down, if you like. You did not indicate whether the provider was the rendering or primary.
I used rendering, so you will see the number of sessions for each provider for each patient (if there are
multiple providers rendering services to a single patient). The output of the query will go to a
Lotus-format file called sessioncount.wks in the C:\SOS folder. Excel will open this file without complaint.

If you want to change the file name or location, modify the last line of the query. (Note that you can
un-comment line 15 if you want to restrict the results to just the IP and GRP service codes. I would
suggest running the more inclusive query first in case the providers are using different codes than you
expect.)

Note that we use JOIN rather than LEFT OUTER JOIN to link the tables in this case. No data will be
omitted because all these values and rows must be present for every charge entry. You can’t have a
charge without a patient (ptnum), provider (providernum), or service (servicenum).

SELECT
   (UPPER(a.LastName)+', '+a.FirstName+' / '+a.id) as "Client",
   c.ProvCode as "Rendering_Provider",
   d.CPTCode,
   d.srvcode,
   COUNT(*) as "NumSessions"
FROM
   sos.patients a
   JOIN sos.jcharges b on a.ptnum = b.ptnum
   JOIN sos.providers c on b.providernum=c.providernum
   JOIN sos.services d on b.servicenum=d.servicenum
WHERE
   a.intakedate > '2003-08-31'
   AND a.flag = 0
   // AND d.srvcode IN ('IP','GRP')
   // remove the beginning slashes on line above to return only those service codes
GROUP BY
   client,
   rendering_provider,
   cptcode,
   srvcode
ORDER BY
   client,
   rendering_provider,
   cptcode,
   srvcode
;OUTPUT TO c:\sos\sessioncount.html FORMAT HTML