SELECT CAST(v_e_s_a.PATID AS INT) As PATIENT_ID,RTRIM(v_e_s_a.c_ss_number) AS SSN, RTRIM(v_e_s_a.patient_gender_code) AS Sex_c,v_e_s_a.c_date_of_birth AS DOB, RTRIM(v_e_s_a.c_ethnicity_code) AS Ethnic_ori,RTRIM(v_e_s_a.c_race_code) AS Race_c, v_e_s_a.EPISODE_NUMBER As Episode,v_e_s_a.preadmit_admission_date AS Adm_padm_d, RTRIM(v_e_s_a.admit_type_of_code) AS Adm_Type_C, CAST(CASE v_e_s_a.FACILITY WHEN 1 THEN 2 WHEN 2 THEN 1 WHEN 3 THEN 7 WHEN 4 THEN 3 WHEN 5 THEN 8 ELSE 0 END AS INT) AS Facility_I, p_c_d.facility_chart_number As Facility_C,v_e_s_a.preadmit_admission_date As Admit_Date, v_e_s_a.date_of_discharge As Discharge_,v_e_s_a.program_code As Program_c,h_l_s.LS_uniqueid As Legal_UID, h_l_s.legal_status_code As Legal_Stat,h_l_s.legal_status_effective_date As Effective_, v_e_s_a.admit_practitioner_name As Adm_Clinician,v_e_s_a.admit_principal_diag_code As Diag_C, v_e_s_a.admit_principal_diag_value As Diag,v_e_s_d.disc_principal_diagnosis_code As Disdiag_c, disc_principal_diagnosis_value As Disdiag,disc_unit_code as Disunit_c,disc_legal_status_value As DisLS, primary_referral_Value As REF_VAL,GUARANTOR_ID As GUAR_ID,guarantor_order_number As GUAR_ORD, guarantor_name As GUAR_NAME,p_c_d.patient_name_last As PAT_NAME_L,p_c_d.patient_name_first As PAT_NAME_F, disc_type_of_value As disc_tval,disc_unit_value As disc_uval,disc_practitioner_staff_name As DiscPname, disc_legal_status_code As disc_LS_c,disc_practitioner_staff_id As DiscPstaf,disc_type_of_code As disc_tcode FROM SYSTEM.view_episode_summary_admit v_e_s_a LEFT OUTER JOIN SYSTEM.patient_current_demographics p_c_d ON v_e_s_a.PATID=p_c_d.PATID AND v_e_s_a.FACILITY=p_c_d.facility LEFT OUTER JOIN SYSTEM.history_legal_status h_l_s ON v_e_s_a.facility=h_l_s.facility AND v_e_s_a.PATID=h_l_s.PATID AND v_e_s_a.EPISODE_NUMBER=h_l_s.EPISODE_NUMBER LEFT OUTER JOIN SYSTEM.view_episode_summary_discharge v_e_s_d ON v_e_s_a.PATID=v_e_s_d.PATID AND v_e_s_a.facility=v_e_s_d.facility AND v_e_s_a.EPISODE_NUMBER=v_e_s_d.EPISODE_NUMBER LEFT OUTER JOIN SYSTEM.Discharge_Referral d_r ON v_e_s_a.PATID=d_r.PATID AND v_e_s_a.EPISODE_NUMBER=d_r.EPISODE_NUMBER AND v_e_s_a.facility=d_r.facility LEFT OUTER JOIN SYSTEM.billing_guar_order_current b_g_o_c ON v_e_s_a.PATID=b_g_o_c.PATID AND v_e_s_a.EPISODE_NUMBER=b_g_o_c.EPISODE_NUMBER AND v_e_s_a.facility=b_g_o_c.facility WHERE admit_type_of_code <> '0' ORDER BY v_e_s_a.c_ss_number, v_e_s_a.preadmit_admission_date, legal_status_effective_date