Category: Management

Mailing Labels by Primary Provider, Pt Category, and Age

Posted on | by SOS Blog | Leave a Comment on Mailing Labels by Primary Provider, Pt Category, and Age

I want to print sets of patient mailing labels, but filtering for specified primary provider codes, patient category, and patient age. Include only patients who have been seen for a chargeable service within the last year.

One effective way to do this is to create a result set that matches what Microsoft Word expects in a mailing list and export it to Excel format. You can then use the Mail Merge wizard in Word to very easily create your labels. Note that we have used the TRIM function on some of the elements to be sure that any extra spaces are removed from the end of the data. We have also used AS to rename the data elements to match what Word is looking for. That saves the step of matching fields when setting up your Mail Merge. This query uses a custom function “AgeInYears” that SOS provides in your database so that you can get accurate age calculations by simply providing the date of birth and the target date. Here we are interested in the patient’s age right now, so instead of hard-coding a date, we use the SQL function TODAY(), which is replaced automatically by the current date when we run the query. The same TODAY() function is used in the condition that restricts the patients to those seen in the past year.

For a video that shows how to do MS Word mail-merge labels using the Excel file produced by this query, go to…
http://www.sosoft.com/files/tv/other/querylabelmerge.swf

SELECT
   TRIM(a.firstname) AS "First Name",
   TRIM(a.lastname) AS "Last Name",
   a.addr1 AS "Address 1",
   a.addr2 AS "Address 2",
   TRIM(city) AS "City",
   TRIM(state) AS "State",
   zip
 FROM  
   sos.rv_patients a
   JOIN sos.ptvars b ON a.ptnum = b.ptnum
 WHERE  
   a.licnum = 101
   AND a.priprvcode IN ('AF','AFB')
   AND a.categcode = 'C'
   AND sos.AgeInYears(a.dob,TODAY() ) BETWEEN 0 AND 80
   AND b.lfeedate > (TODAY() - 365)
 ORDER BY
   a.lastname, a.firstname
 ;
 OUTPUT TO c:\sos\labels.html FORMAT HTML

Listing Patients With UserSort Fields

Posted on | by SOS Blog | Leave a Comment on Listing Patients With UserSort Fields

I am trying to do a query on the additional tab of a client.
We have renamed the 3 squares (in a row) and not sure what they were called Can you give me a query that will get any of the 3 squares. I would never run them all at the same time however would run 1 at a time.

The three customizable fields on the Additional Tab are “usersort”, “usersort2”, and “usersort3”. You can find them in the PATIENTS table and in the RV_PATIENTS view. The example below includes all three, but you can remove any you do not want from the SELECT list:

SELECT
  lastname,firstname,id,usersort,usersort2,usersort3
FROM
  /*you can select from either PATIENTS or RV_PATIENTS*/
  sos.patients
WHERE
  /*the following conditions filter out all but active patients in the main data set */
  flag = 0
  AND dischargedate IS NULL
  AND licnum = 101
ORDER BY
  lastname,firstname,id

List Next Treatment Plan Reviews

Posted on | by SOS Blog | Leave a Comment on List Next Treatment Plan Reviews

This query demonstrates the use of a subquery to create values in a column — nextreviewdate, in this case. Adjust the date range as needed.

SELECT
    (DATE(COALESCE(
        (SELECT MIN(nextreviewdate)
                 FROM sos.v_tpreviews
                 WHERE ptepisodenum = b.ptepisodenum
                 AND tpheadernum = c.tpheadernum
                 AND spv_signandfinalize = 1
                 AND rowstatus = 'O' ),c.nextreviewdate)
    )) AS nextreviewdate,
    (a.lastname+', '+a.firstname+' / '+a.id) AS "Patient",
    d.provcode AS "Primary Provider"
FROM
  sos.patients a
  JOIN sos.ptepisodes b ON a.ptnum = b.ptnum
  JOIN sos.tpheaders c ON b.ptepisodenum = c.ptepisodenum
  LEFT OUTER JOIN sos.providers d ON b.providernum = d.providernum
WHERE
   a.flag = 0
   AND b.currentflag = 1
   AND a.dischargedate IS NULL
   AND nextreviewdate BETWEEN '2009-09-01' AND '2009-09-30'
ORDER BY "NextReviewDate","Patient"

Mailing Labels For Payors In Collections

Posted on | by SOS Blog | Leave a Comment on Mailing Labels For Payors In Collections

I need mailing labels for Payors with a collection date of 1/1/11 and greater, excluding accounts that are paid in full.

The date that a payor is sent to collections is a field in the payors table. The balance owed by the payor is a sum of the field “postedbal” in a related table, ptpayors. The primary key that uniquely identifies a payor is the payornum value. JOIN the two tables together using this shared value, payornum, which is found in both tables.

There may be more than one ptpayors row for each payor, so to eliminate duplicate rows in the output (which will subsequently be loaded into Excel and used as data for a MailMerge label in Word), we add the DISTINCT modifier after SELECT. That will have the effect of removing any duplicate rows.

The COALESCE function in the collection date condition is there to assign a value to payors with NULL collection dates. We use an arbitrary date that should always be outside of the desired selection range to prevent those rows from being included in the results or preventing any results from being delivered because of the ambiguous nature of NULL values.

SELECT DISTINCT
   TRIM(a.firstname) AS "First Name",
   TRIM(a.payorname) AS "Last Name",
   a.addr1 AS "Address 1",
   a.addr2 AS "Address 2",
   TRIM(city) AS "City",
   TRIM(state) AS "State",
   zip
 FROM  
   sos.payors a
   JOIN sos.ptpayors b ON a.payornum = b.payornum
 WHERE  
   COALESCE(CollectDate,'1990-01-01') >= '2011-01-01'
   AND b.postedbal > 0
 ORDER BY
   "Last Name", "First Name"
 ;
 OUTPUT TO c:\sos\labels.html FORMAT HTML

Intakes for Period with Primary Dx, Primary Provider, and Office Location

Posted on | by SOS Blog | Leave a Comment on Intakes for Period with Primary Dx, Primary Provider, and Office Location

I need a query that can be done by date range for charge with a CPT code of 90801 and will print out the ID, Name, Primary Dx, Primary Provider, and Office location (which is taken from Box 32 in the CSU that is attached to the charge).

SELECT
  a.id AS "ID",
  (a.lastname+', '+a.firstname) AS "Name",
  e.dxcode AS "Primary Dx",
  f.provcode AS "Primary Provider",
  g.shorthand AS "Office Location"

FROM
  sos.patients a
  JOIN sos.journal b ON a.ptnum = b.ptnum
  JOIN sos.jcharges c ON b.jnum = c.jnum
  JOIN sos.ptcsu d ON c.ptcsunum = d.ptcsunum
  LEFT OUTER JOIN sos.dx e ON d.dx1 = e.dxnum
  LEFT OUTER JOIN sos.providers f ON a.providernum = f.providernum
  LEFT OUTER JOIN sos.facilities g ON d.facilitynum = g.facilitynum
  JOIN sos.services h ON c.servicenum = h.servicenum
WHERE
   h.cptcode = '90801'
   AND b.trandate BETWEEN '2008-01-01' AND '2008-12-31'
ORDER BY "Name"