SQL Logic: RevFlow New Patient
Use the following logic to filter data to only include new patients.
select distinct c.company_name as "Brand Partner", pos.pos_name as "Location", c.company_id as "Company ID", pos.place_of_service_id as "POS ID", p.patient_id as "Account #", p.first_name as "First Name", p.last_name as "Last Name", p.dob as "DOB", p.phone as "Phone", p.email as "Email", v.date_of_service as "DOS", rp.last_name ||', '|| rp.first_name AS "Referring Physician", rp.npi as "NPI", ca.injury_desc as "Case Desc" from "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_D_VISIT" v join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_D_PATIENT" p on v.patient_id = p.patient_id join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_A_COMPANY" c on v.company_id = c.company_id join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_C_PLACE_OF_SERVICE" pos on v.pos_code = pos.place_of_service_id::varchar join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_C_REFERRING_PHYSICIAN" rp on v.md_code = rp.referring_physician_id join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_D_CHARGE" ch on p.patient_id = ch.patient_id join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_C_PROCEDURE_CODE" pc\ on pc.proc_code = ch.proc_code and pc.company_id = ch.company_id join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_C_REVENUE_CENTER" rc\ on rc.revenue_center_id = pc.revenue_center_id\ and rc.revenue_center_code = '1' join "REPORTING_DB"."PUBLIC"."SVW_REVFLOW_D_CASE" ca\ on v.case_id = ca.case_id where ch.date_of_Service::Date between '2/1/2021' and '2/15/2021' and gpms_code in () and rc.revenue_center_code = 1 and ch.is_Active='True'