Simple Export of Basic Patient Information

I need to export the following fields in Excel or .csv format:

Last name, first name, address (all fields), primary phone, email (optional), DOB, sex, primary provider, diagnosis, primary insurance carrier, insured ID#

Instead of going directly to CSV, we recommend exporting in HTML format instead. You can do a quick check on the results by just double-clicking the file (in this example, the results are saved in the file name MYEXPORT.HTML in the SOS folder). You can then open the file in Excel by starting Excel and using File > Open, being careful to select the appropriate file type. In Excel 2013 the type is “All Web Pages”. The results will appear with each data element in its own column and a heading at the top of each column. At that point, you can manipulate the data as you like and save as CSV or any other format supported by Excel.

Here is the query, including the OUTPUT line that saves the results as an HTML file. Note that the WHERE clause filters the results to include only patients in dataset 101 (licnum = 101), who appear in the Active Patient List (flag = 0), and who have not been discharged (discharge date IS NULL). You can change or remove any of these filters as needed, or add new ones.

SELECT
  a.lastname
  ,a.firstname
  ,a.addr1
  ,a.addr2
  ,a.city
  ,a.state
  ,a.zip
  ,(a.phone1area+'-'+a.phone1) AS phone
  ,a.dob
  ,a.sex
  ,b.provlname+', '+b.provfname AS primary_provider
  ,d.dxcode
  ,c.payorname AS primary_insurance
  ,c.insdid AS insured_id
FROM
  sos.rv_patients a
  JOIN sos.providers b ON a.priprvcode = b.provcode
  JOIN sos.rv_policies c ON a.ptnum = c.ptnum
  JOIN sos.dx d ON a.dx1=d.dxnum
WHERE
  a.licnum = 101
  AND b.licnum = 101
  AND a.flag = 0
  AND a.dischargedate IS NULL
  AND c.inspos = 1
ORDER BY
  a.lastname,a.firstname,a.id
;
OUTPUT TO c:\sos\myexport.html FORMAT html
;