Category: Management

Admissions by Zipcode

Posted on | by SOS Blog | Leave a Comment on Admissions by Zipcode

Start with the basic count of intakes between two dates, grouped by zip code:

SELECT
   zip,
   COUNT(*) AS "N"
FROM 
   sos.rv_patients
WHERE
   intakedate BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY 
   zip
ORDER BY 
   zip

Next, let’s make it a little more interesting by breaking the results down further by year and month:

SELECT
   zip,
   YEAR(intakedate) AS "Yr",
   MONTH(intakedate) AS "Mon",
   COUNT(*) AS "N"
FROM 
   sos.rv_patients
WHERE
   intakedate BETWEEN '1990-01-01' AND '2005-12-31'
GROUP BY 
   zip, 
   "Yr", 
   "Mon"
ORDER BY 
   zip, 
   "Yr", 
   "Mon"

You could, of course, change the order of the columns in the SELECT clause and in the ORDER BY line
to reorganize the output.

Length of Stay by Provider

Posted on | by SOS Blog | 2 Comments on Length of Stay by Provider

Let’s take a look at this request from a detailed and summary angle.

SELECT
   Lastname,
   firstname,
   id,
   COALESCE(provcode,'None') AS "Provider",
   Intakedate,dischargedate,
   DateDiff(day,intakedate,dischargedate) AS "Days"
FROM 
   sos.patients a
   JOIN sos.providers b ON a.providernum=b.providernum
WHERE
   intakedate IS NOT NULL
   and dischargedate IS NOT NULL
....will give you a list of patients, their intake and discharge dates, and the number of days between those
dates.

Extending that to give us a summary, with provider, average length of stay, and the number of patients
from which the statistics are derived:
SELECT
   COALESCE(provcode,'None') AS "Provider",
   COUNT(*) AS "N",
   AVG(DateDiff(day,intakedate,dischargedate)) AS "Days"
FROM 
   sos.patients a
   JOIN sos.providers b ON a.providernum=b.providernum
WHERE
   intakedate IS NOT NULL
   and dischargedate IS NOT NULL
GROUP BY 
   "Provider"
ORDER BY 
   "Provider"

Referral Source, Patient Count and Charges By Month

Posted on | by SOS Blog | Leave a Comment on Referral Source, Patient Count and Charges By Month

The unique thing about this query is that it creates a grid with the months across the top. For each
Referral Source/Year there is a single row.

SELECT 
   b.refname as "Ref’d By", 
   YEAR(c.trandate) as "Year",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 1 THEN c.ptnum ENDIF)) as "Jan Count",
   SUM(IF MONTH(c.trandate) = 1 THEN c.amount ELSE 0 ENDIF) as "Jan Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 2 THEN c.ptnum ENDIF)) as "Feb Count",
   SUM(IF MONTH(c.trandate) = 2 THEN c.amount ELSE 0 ENDIF) as "Feb Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 3 THEN c.ptnum ENDIF)) as "Mar Count",
   SUM(IF MONTH(c.trandate) = 3 THEN c.amount ELSE 0 ENDIF) as "Mar Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 4 THEN c.ptnum ENDIF)) as "Apr Count",
   SUM(IF MONTH(c.trandate) = 4 THEN c.amount ELSE 0 ENDIF) as "Apr Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 5 THEN c.ptnum ENDIF)) as "May Count",
   SUM(IF MONTH(c.trandate) = 5 THEN c.amount ELSE 0 ENDIF) as "May Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 6 THEN c.ptnum ENDIF)) as "Jun Count",
   SUM(IF MONTH(c.trandate) = 6 THEN c.amount ELSE 0 ENDIF) as "Jun Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 7 THEN c.ptnum ENDIF)) as "Jul Count",
   SUM(IF MONTH(c.trandate) = 7 THEN c.amount ELSE 0 ENDIF) as "Jul Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 8 THEN c.ptnum ENDIF)) as "Aug Count",
   SUM(IF MONTH(c.trandate) = 8 THEN c.amount ELSE 0 ENDIF) as "Aug Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 9 THEN c.ptnum ENDIF)) as "Sep Count",
   SUM(IF MONTH(c.trandate) = 9 THEN c.amount ELSE 0 ENDIF) as "Sep Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 10 THEN c.ptnum ENDIF)) as "Oct Count",
   SUM(IF MONTH(c.trandate) = 10 THEN c.amount ELSE 0 ENDIF) as "Oct Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 11 THEN c.ptnum ENDIF)) as "Nov Count",
   SUM(IF MONTH(c.trandate) = 11 THEN c.amount ELSE 0 ENDIF) as "Nov Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 12 THEN c.ptnum ENDIF)) as "Dec Count",
   SUM(IF MONTH(c.trandate) = 12 THEN c.amount ELSE 0 ENDIF) as "Dec Chgs",
   COUNT(DISTINCT a.ptnum) as "# Count", SUM(c.amount) as "TotChgs"
FROM 
   sos.patients a
   JOIN sos.refsrcs b on a.refsrcnum = b.refsrcnum
   JOIN sos.journal c on a.ptnum = c.ptnum
WHERE 
   c.trantype = 'S' 
   AND c.trandate > '2000-12-31'
GROUP BY 
   b.refname, 
   YEAR(c.trandate)
ORDER BY 
   b.refname, 
   YEAR(c.trandate)

Number of Services for Patients by Service Code and Rendering Provider

Posted on | by SOS Blog | Leave a Comment on 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

Patients by Last Four Digits of Social Security Number

Posted on | by SOS Blog | Leave a Comment on Patients by Last Four Digits of Social Security Number 
Recently we had an audit done by an outside agency that used the last 4 digits of the SS# to identify the
patient charges they chose to audit. We have a need to take a look at several of those charts, but the
only identifying data we now have is the last 4 SS# digits. How can I retrieve the patients with just this
information?
The following query should provide you with what you need. Just replace 1234 in the last line with the
digits you want to search. Don't neglect the single quotes around the number:
SELECT
   lastname, 
   firstname, 
   id, 
   socsec
FROM
   sos.patients
WHERE
   RIGHT(socsec,4) = '1234'
If you have a list of numbers you would like to do in a single shot, you can modify it as follows:
SELECT
   lastname, 
   firstname, 
   id, 
   socsec, 
   RIGHT(socsec,4) as "last4"
FROM
   sos.patients
WHERE
   last4 IN ('1234','5678','2468','4321')
or, for a complete list of patients, sorted by the last four digits:
SELECT
   RIGHT(socsec,4) as "last4", 
   lastname, 
   firstname, 
   id, 
   socsec
FROM
   sos.patients
ORDER BY 
   last4