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