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 ''
;

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.