Category: G4 Queries

Active Patient Count For Date Range

Posted on | by SOS Blog | Leave a Comment on Active Patient Count For Date Range

This simple query tells you how many patients have charge entries in their ledgers between two dates.
The keyword DISTINCT in the COUNT() function eliminates duplicates.

SELECT
   COUNT(DISTINCT ptnum)
FROM
   sos.journal
WHERE
   trantype = 'S'
   AND amount > 0
   AND trandate BETWEEN '2004-01-01' AND '2004-12-31'

Here’s a variation using the rv_charges view that breaks down the patient count by rendering provider. In
this case, we use rv_charges as an easy way to get to the provider code associated with the charges:

SELECT
   provcode AS "Provider", 
   COUNT(DISTINCT ptnum) AS "Clients Seen"
FROM
   sos.rv_charges
WHERE
   amount > 0
   AND trandate BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY
   provcode

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)

Referring Physicians for Payors with ID’s and Patient Count

Posted on | by SOS Blog | Leave a Comment on Referring Physicians for Payors with ID’s and Patient Count 
I'm writing a query and want to print out medicare and bcbs numbers in addition to default id for referral
sources. I can get the defaultid from refsrcs but the other numbers come up as null. I upgraded to the
latest version of OM and it now looks like there is a different system for entering specific insurance
numbers for referral sources so I was wondering if this is a factor? Also is there a way to get a count for
the number of patients a particular referring physician has sent our way?
OK, looks like you just want referral sources associated with active patients based on intake date. Couple
of things you need to consider...

Firstly, the referring physicians used on claims may not match the referral source stored in Patients. The
latter could be "yellow pages", former patients, or whatever, while the former should be only physicians
or other healthcare providers. Given that you are interested in the ID's, it seems that you are probably
more interested in the referring physician that prints on the claim forms. That is in the claim setup table
(PtCSU) not in Patients. To get to those, you would have to add a JOIN to PtCSU. In addition you will
have to (left outer) join refpayorids and payors:
SELECT
 trim(refname + ', ' + b.firstname) as "RefName",
 b.address1,
 b.address2,
 b.city,
 b.zip,
 b.phone,
 b. Defaultid,
 e.payorname,
 d.providerid,
 d.secondaryid,
 COUNT(distinct a.ptnum) AS "PtCount"
FROM
 sos.patients A
 JOIN sos.ptcsu c ON c.ptnum = a.ptnum
 JOIN sos.refsrcs b ON b.refsrcnum = c.refsrcnum
 LEFT OUTER JOIN sos.refpayorids d ON b.refsrcnum = d.refsrcnum
 LEFT OUTER JOIN sos.payors e ON e.payornum = d.payornum
WHERE
 a.flag=0
 AND a.intakedate > '1980-10-01'
 AND a.licnum='101'
 AND e.payornum IN (101,102)
GROUP BY
 refname,
 b.address1,
 b.address2,
 b.city,
 b.zip,b.phone,
 b.defaultid,
 e.payorname,
 d.providerid,
 d.secondaryid
ORDER BY
 refname
;OUTPUT TO c:\sos\refinfo.html FORMAT HTML