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