Category: Queries – Patients

Mailing Labels for Patients with Specific Insurance Payors

Posted on | by SOS Blog | Leave a Comment on Mailing Labels for Patients with Specific Insurance Payors 
We need to send letters to all of our psychiatrists' patients catagorized by insurance payor. How can I
get mailing labels for these letters? The scenario is that our docs have removed themselves from the
Magellan and United Behavioral Health panels and will only accept fee-for-services from those patients.
Obviouly we need to inform the patients - several times and in several ways. The first step is to identify
them and prepare labels for the letters to be sent.

OK, so we can identify the patients using the primary provider code. Good. Let's start with this query. I'll
include the payorname in the output so you can check the export. You can ignore that column when you
read the data into Word to create your labels.
SELECT
   (a.firstname + ' '+a.lastname) as "name",
   a.addr1 as "addr1",
   a.addr2 as "addr2",
   (a.city+', '+a.state+' '+a.zip) as "addr3",
   b.payorname as "payor"
FROM
   sos.rv_patients a 
   join sos.rv_policies b on a.ptnum = b.ptnum
WHERE
   a.flag = 0 
   and a.priprvcode='prv' 
   and (b.payorname like '%magellan%' or b.payorname like '%ubh%') 
   and (b.active is null or b.active <= current date) 
   and (b.inactive is null or b.inactive >= current date)
; output to c:\sos\labels.csv format ascii
The resulting csv file can be loaded into Excel for additional manipulation, if you like, or can be used as
is. If you check the help in Word regarding mailing labels, that should take you the rest of the way. Oh,
note the fourth line up from the bottom. This one is where I am specifying the payor names. If you have
a UBH payor that you have spelled out as "United Behavioral Health" you should add another "or...." to
that condition, or change the '%ubh%' condition. This expression means that it will include payors with
the characters "ubh" anywhere in the name. That could result in some payors you do not want, such as a
payor named "Flubhead" or something like that. You should be able to fine tune that with a little
experimentation. On the fifth line up from the bottom you must replace 'prv' with your psychiatrist's
provider code. The other conditions restrict the output to active patients with active matching policies as
of today's date.

Active Patients with No Service in Last 90 Days

Posted on | by SOS Blog | Leave a Comment on Active Patients with No Service in Last 90 Days 
SELECT 
   "name/id", 
   lfeedate as 'Last Service', 
   priprvcode as 'Primary Provider'
FROM 
   sos.rv_patients
WHERE 
   flag = 0 
   and lfeedate < (current date - 90)
You can, of course, change the date range by altering the number in the final expression. The condition
“flag = 0” restricts the output to active patients. The quotes around the column name “name/id” are
required so that the query processor does not try to divide name by id. When you enclose a string within
quotes it is treated as a column name, so embedded special characters are ignored. In this case the view
RV_PATIENTS contains an expression that creates this convenient compound value from the firstname,
lastname, and id fields.

	


	





	

			

	

		
Count Service Dates for Patients, Grouped by Rendering Provider
		

			Posted on  |  by SOS Blog |  Leave a Comment on Count Service Dates for Patients, Grouped by Rendering Provider		

			

	

		
“Any suggestions on how to create a query that would provide a count of the Dates Of Service for each
patient within a specified date range? I would also need either rendering provider or primary.


SELECT
   id, 
   provcode, 
   count(distinct trandate) as SrvDateCount
FROM
   sos.rv_charges
WHERE
   trandate between '2001-01-01' and '2001-03-31'
GROUP BY
   id, 
   provcode


Obviously set the date range correctly. Note that the DISTINCT in this case must be within the parens
with the column you want the distinct count of.

	


	





	

			

	

		
Patient List by Rendering Provider
		

			Posted on  |  by SOS Blog |  Leave a Comment on Patient List by Rendering Provider		

			

	

		
Here is a query that will show the patient on the list for every provider who ever provided services for
that patient (so that patient could show up on multiple lists). The output is sorted by rendering provider,
but there are no page breaks or other fancy stuff.


SELECT DISTINCT
   d.provcode,
   a.lastname, 
   a.firstname, 
   a.id
FROM
   sos.patients a 
   JOIN sos.journal b 
   JOIN sos.jcharges c 
   JOIN sos.providers d
WHERE
   a.flag = 0
GROUP BY
   provcode,
   lastname, 
   firstname, 
   id
ORDER BY
   provcode, 
   lastname, 
   firstname, 
   id


"a.flag = 0" restricts the output to active patients
If you wanted to output a single provider you would add this on the line below "a.flag"
AND d.provcode = 'ABC'

	


	





	

			

	

		
Patients with Zero Balance
		

			Posted on  |  by SOS Blog |  Leave a Comment on Patients with Zero Balance		

			

	

		
“Is there a way to print out a list of the active clients who have
zero balances, without printing out a list of ALL of the active clients?
Having this list would make it easy to find the clients who could be moved
to the "inactive" list.”

List of active accounts...


SELECT 
   LastName, 
   FirstName, 
   ID
FROM 
   sos.patients
WHERE 
   flag = 0 
   AND dischargedate IS NULL
ORDER BY 
   LastName, 
   FirstName, 
   ID


List of active accounts with zero balance...


SELECT 
   LastName, 
   FirstName, 
   ID
FROM 
   sos.Patients a JOIN sos.PatientBalance b on a.ptnum = b.ptnum
WHERE 
   b.ptbalance = 0 
   And a.flag = 0 
   AND a.dischargedate IS NULL
ORDER BY 
   LastName, 
   FirstName, 
   ID


List of discharged patients that have no balance...


SELECT 
   LastName, 
   FirstName, ID
FROM 
   sos.Patients a
   JOIN sos.PatientBalance b on a.ptnum = b.ptnum
WHERE 
   b.ptbalance = 0
   AND a.flag = 0
   AND a.dischargedate IS NOT NULL
ORDER BY 
   LastName, FirstName, ID


If you wanted to list by a particular Patient Category, you would
have to use a second table, PtCategs, and you would have to add the
additional condition to the WHERE clause of the query. Here's an example,
assuming the first patient category you want is "ONE":


SELECT 
   a.LastName, 
   a.FirstName, 
   a.ID
FROM 
   sos.patients a
   JOIN sos.ptcategs b ON a.ptcategnum = b.ptcategnum
WHERE 
   a.flag = 0 
   AND a.dischargedate IS NULL 
   AND b.categcode = 'ONE'
ORDER BY 
   LastName, 
   FirstName, 
   ID


The other approach is to add the patient category to the output and sort the
data on that value as well. You would then divide up the report in your word
processor before outputting it:


SELECT 
   b.CategCode, 
   a.LastName, 
   a.FirstName, 
   a.ID
FROM 
   sos.patients a
   JOIN sos.ptcategs b ON a.ptcategnum = b.ptcategnum
WHERE 
   a.flag = 0 
   AND a.dischargedate IS NULL 
   AND b.categcode = 'ONE'
ORDER BY 
   b.CategCode, 
   a.LastName, 
   a.FirstName, 
   a.ID