Category: Queries – Providers

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

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"

This query delivers a list of discharged patients with rendering provider and discharge date. If the patient was seen by more than one provider, he or she will appear multiple times in the list:

SELECT
  DISTINCT pt.lastname, pt.firstname, pt.id, pr.provcode, pt.dischargedate
FROM
  sos.providers pr
  JOIN sos.jcharges jc ON jc.providernum = pr.providernum
  JOIN sos.patients pt ON jc.ptnum = pt.ptnum
WHERE
  pt.dischargedate IS NOT NULL
ORDER BY
  pt.lastname, pt.firstname, pt.id, pr.provcode

To sort by provider, rather than patient name, just change the ORDER BY clause:

SELECT
  DISTINCT pt.lastname, pt.firstname, pt.id, pr.provcode, pt.dischargedate
FROM
  sos.providers pr
  JOIN sos.jcharges jc ON jc.providernum = pr.providernum
  JOIN sos.patients pt ON jc.ptnum = pt.ptnum
WHERE
  pt.dischargedate IS NOT NULL
ORDER BY
  pr.provcode, pt.lastname, pt.firstname, pt.id