Category: Queries – Patients

Patient Export For Rx Service

Posted on | by SOS Blog | Leave a Comment on Patient Export For Rx Service

I have just signed up for an e-prescribing system. It can upload/populate patients from a csv file rather than having to re-enter them. Is there a query or how can I get SOS to give me a csv file with just: name, account ID, DOB, address, phone number, gender, and most recent modification date and time? I want to restrict the patient listing to just those patients being seen for medication visits?

Sure. The following query gathers the desired information for just patients being seen for outpatient services that might include medication (implied by the cpt code and the type of rendering provider).  The OUTPUT statement below the query writes the results to the filename specified on that line. As written, the values will be separated by commas, with no quotation marks around the values. If you want quotes or some other delimiter around the string values, then insert the desired character between the two apostrophes at the end of the line.

SELECT
  pt.firstname AS "FirstName",
  pt.midinit AS "MiddleInit",
  pt.lastname AS "LastName",
  pt.id AS "AccountID",
  pt.dob AS "DOB",
  pay.addr1 AS "Address1",
  pay.addr2 AS "Address2",
  pay.city AS "City",
  pay.state AS "State",
  pay.zip AS "Zip",
  (IF length(TRIM(pay.phone1))=8 
   THEN pay.phone1area + '-' + pay.phone1 
   ELSE '' 
   ENDIF) AS "Phone",  -- first phone number
  pt.sex AS "Sex",
  pt.adddate AS "AddDate",
  pt.addtime AS "AddTime",
  pt.upddate AS "LastModDate",
  pt.updtime AS "LastModTime"
FROM
  sos.patients pt JOIN sos.payors pay ON pt.payornum=pay.payornum
WHERE 
  -- just active pts without discharge dates
  pt.flag = 0
  AND pt.dischargedate is null
  -- in the main dataset
  AND pt.licnum = 101
  -- who have been seen in the past 60 days
  -- by a medical provider
  -- for certain cpt codes
  -- in an outpatient setting
  AND ptnum IN 
     (SELECT 
        j.ptnum 
      FROM 
        sos.journal j
        JOIN sos.jcharges a ON j.jnum = a.jnum
        JOIN sos.poscodes b ON a.poscodenum = b.poscodenum
        JOIN sos.services c ON a.servicenum = c.servicenum
        JOIN sos.providers prv ON pt.providernum = prv.providernum 
        JOIN sos.provtype prt ON prv.provtypenum = prt.provtypenum
      WHERE 
        (c.cptcode IN ('90862','90805','90807','90809','90801') OR c.cptcode LIKE '99%')
        AND prt.provtypecode IN ('PA','ARNP','MD')
        AND b.defcode NOT IN ('21','51','61')
        AND j.trandate BETWEEN (TODAY()-60) AND TODAY()
      )
;
OUTPUT TO c:\SOS\rxexport.csv FORMAT ASCII QUOTE ''
;

Outstanding Account Cleanup

Posted on | by SOS Blog | Leave a Comment on Outstanding Account Cleanup

Would it be possible to write a query to do the following:

List the patient name, account number, outstanding balance and provider
For any account that has not had a date of service in 2009
And has had no payments within the past 30 days.

We want to use this to clear out all such outstanding accounts..

The views used in the query below are not super-efficient, so on a large database it will take a good while to run, but it will deliver the results you want.

SELECT
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT sos.LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT sos.LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM sos.patients a
LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
JOIN sos.patientbalance d ON a.ptnum = d.ptnum
WHERE
"LastService" < '2009-01-01'
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.html FORMAT HTML
;

Non-Insurance Balance By Place of Service, Date Range, and Provider

Posted on | by SOS Blog | Leave a Comment on Non-Insurance Balance By Place of Service, Date Range, and Provider

I am looking for a query that prints out total balance remaining on patients only (not insurance) by LOC code (e.g.,11 or 61) by date range by provider.

The following query gives the balance itemized by patient. To get just summary totals, remove “a.lastname,a.firstname,a.id” from the SELECT and GROUP BY clauses.

SELECT
a.provcode,a.lastname,a.firstname,a.id,SUM(a.chgsplbal) AS "Balance"
FROM
sos.rv_charges a
JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN sos.payors c ON b.payornum = c.payornum
JOIN sos.poscodes d ON a.poscodenum = d.poscodenum
JOIN sos.patients e ON a.ptnum = e.ptnum
WHERE
a.licnum = 101 AND   /*look only at main data set*/
e.flag = 0 AND          /* just active list patients*/
c.payortype <> 'I'     /* ignore insurance splits*/
AND d.defcode IN ('11','61')    /* place of service code is 11 or 61*/
AND a.trandate BETWEEN '2009-01-01' AND '2009-03-31'    /* date range*/
GROUP BY
a.provcode,a.lastname,a.firstname,a.id

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