SELECT p.Patient_Id, p.Pt_Agy_ID, p.Pt_Last, p.Pt_First, p.Pt_Middle, p.Status, p.CBSA_Code,an.F26
,p.Pt_Zip,p.Pt_Street, p.Pt_City, p.Pt_State,p.Pt_Phone,p.Pt_DOB ,p.Email, p.Loc_ID,ag.Agency_Name,pa.Notes, pa.M0030_START_CARE_DT AS Start_of_Care_M0030
,pp.Payer_Id, an.Assmt_ID, an.F177, an.F178, an.F196,an.F176, an.F25 AS admission_status, an.F181 AS Date_Discharge_M0906, py.Pay_Name, an.Assmt_ID AS Previous_Assessment_Number,an.Assmt_ID,pa.Admit_ID ,
e.Emp_First,e.Emp_Last ,ph.Phys_ID,py.Biller_Type
FROM #Request.prefix_db_agency#.pAssessments an
LEFT OUTER JOIN #Request.prefix_db_agency#.pPatients p ON p.Patient_ID = an.Patient_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit pa ON p.Patient_ID = pa.Patient_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.pPhysicians ph ON ph.Phys_ID = pa.Refer_Phys_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee e ON e.Emp_ID = ph.Emp_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer pp ON pp.Patient_ID = p.Patient_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer py ON pp.Payer_ID = py.Pay_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.Agency ag ON p.Loc_ID = ag.Agency_ID
WHERE p.Loc_ID = '#session.AgencyId#'
AND p.Status = 1
AND an.Status = 0
AND pp.Status = 0
AND py.Status = 0
AND pa.Status = 0
AND py.Biller_Type IN (
)
Group by p.Patient_ID
ORDER BY p.Pt_Last, p.Pt_First, M0030_START_CARE_DT ASC