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_City, p.Pt_State, p.Loc_ID,ag.Agency_Name,pa.M0030_START_CARE_DT ,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 ,si.Visit_Date, si.Pay_ID, py.Pay_Name, r.Referral_Name, CONCAT(IFNULL(e.Emp_Last,''), ', ', IFNULL(e.Emp_First,'')) AS Employee_Name, ( SELECT CONCAT(IFNULL(esocv.Emp_Last,''), ', ', IFNULL(esocv.Emp_First,'')) FROM #Request.prefix_db_agency#.pSchedules ssoc LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee esocv ON esocv.Emp_ID = ssoc.Emp_ID WHERE ssoc.Patient_ID = an.Patient_ID AND ssoc.Status = 0 AND IFNULL(ssoc.Missed, 0) = 0 AND IFNULL(ssoc.Emp_ID, 0) > 0 AND ssoc.Visit_Type LIKE '1%' ORDER BY ssoc.Schedule_ID ASC LIMIT 1 ) AS SOC_Employee_Name, ( SELECT CONCAT(IFNULL(ev.Emp_Last,''), ', ', IFNULL(ev.Emp_First,'')) FROM #Request.prefix_db_agency#.pSchedules sv LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ev ON ev.Emp_ID = sv.Emp_ID WHERE sv.Patient_ID = an.Patient_ID AND sv.Status = 0 AND IFNULL(sv.Missed, 0) = 0 AND IFNULL(sv.Emp_ID, 0) > 0 AND sv.Visit_Type IN ( '6 - Transfer no discharge', '7 - Transfer discharged', '8 - Death', '9 - Discharge from agency' ) AND (DATE(sv.Visit_Date) = DATE(an.F176) OR DATE(sv.Visit_Date) = DATE(an.F181)) ORDER BY sv.Schedule_ID ASC LIMIT 1 ) AS Visit_Completed_By_Name, si.Schedule_ID,an.Assmt_ID AS Previous_Assessment_Number,d.Description FROM #Request.prefix_db_agency#.pSchedules si LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments an ON an.Assmt_ID = si.Assmt_ID 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_agency#.pReferral r ON r.Referral_ID = pa.Referral_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pProblem d on d.Assmt_ID = an.Assmt_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#.pEmployee e ON e.Emp_ID = an.Record_Created_By LEFT OUTER JOIN #Request.prefix_db_agency#.Agency_Payroll l ON l.Agency_ID = p.Loc_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.Agency ag ON l.Agency_ID = ag.Agency_ID WHERE p.Loc_ID = '#session.AgencyId#' AND si.Status = 0 AND p.Status < 2 AND an.Status = 0 AND pp.Status = 0 AND py.Status = 0 AND si.Pay_ID = 0 AND pa.Status = 0 AND d.Status = 0 AND d.M0_No like 'M102%' AND an.F26 IN ( 'Discharge Non-OASIS' ) AND an.F177 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND LEFT(an.F26,1) IN ( ) AND LEFT(an.F26,1) IN (6,7,8,9) AND an.F181 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND an.F26 = 'Discharge Non-OASIS' AND an.F176 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND an.F26 IN ( '1 - Start of Care - further visits planned' ) AND an.F196 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND si.Visit_Date BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAssessments soc WHERE soc.Admit_ID = pa.Admit_ID AND soc.Status = 0 AND soc.F25 = 1 AND soc.F26 LIKE '1%' AND soc.Record_Created_By IN ( ) ) AND py.Biller_Type IN ( ) Group by #groupby# Order by #orderby# 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_Race ,p.Pt_Zip, p.Pt_City, p.Pt_State, p.Loc_ID,ag.Agency_Name,pa.M0030_START_CARE_DT, 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, r.Referral_Name, CONCAT(IFNULL(e.Emp_Last,''), ', ', IFNULL(e.Emp_First,'')) AS Employee_Name, ( SELECT CONCAT(IFNULL(esocv.Emp_Last,''), ', ', IFNULL(esocv.Emp_First,'')) FROM #Request.prefix_db_agency#.pSchedules ssoc LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee esocv ON esocv.Emp_ID = ssoc.Emp_ID WHERE ssoc.Patient_ID = an.Patient_ID AND ssoc.Status = 0 AND IFNULL(ssoc.Missed, 0) = 0 AND IFNULL(ssoc.Emp_ID, 0) > 0 AND ssoc.Visit_Type LIKE '1%' ORDER BY ssoc.Schedule_ID ASC LIMIT 1 ) AS SOC_Employee_Name, ( SELECT CONCAT(IFNULL(ev.Emp_Last,''), ', ', IFNULL(ev.Emp_First,'')) FROM #Request.prefix_db_agency#.pSchedules sv LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ev ON ev.Emp_ID = sv.Emp_ID WHERE sv.Patient_ID = an.Patient_ID AND sv.Status = 0 AND IFNULL(sv.Missed, 0) = 0 AND IFNULL(sv.Emp_ID, 0) > 0 AND sv.Visit_Type IN ( '6 - Transfer no discharge', '7 - Transfer discharged', '8 - Death', '9 - Discharge from agency' ) AND (DATE(sv.Visit_Date) = DATE(an.F176) OR DATE(sv.Visit_Date) = DATE(an.F181)) ORDER BY sv.Schedule_ID ASC LIMIT 1 ) AS Visit_Completed_By_Name, an.Assmt_ID AS Previous_Assessment_Number,an.Assmt_ID,pa.Admit_ID 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_agency#.pReferral r ON r.Referral_ID = pa.Referral_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#.pEmployee e ON e.Emp_ID = an.Record_Created_By 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 < 2 AND an.Status = 0 AND pp.Status = 0 AND py.Status = 0 AND pa.Status = 0 AND an.F26 IN ( 'Discharge Non-OASIS' ) AND an.F177 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND LEFT(an.F26,1) IN ( ) AND LEFT(an.F26,1) IN (6,7,8,9) AND an.F181 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND an.F26 like '6%' AND an.F181 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND an.F26 IN ( '1 - Start of Care - further visits planned' ) AND an.F196 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND an.F25 = 0 AND an.F177 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND an.F177 BETWEEN #CreateODBCDate(Form.From_Date)# AND #CreateODBCDate(Form.To_Date)# AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAssessments soc WHERE soc.Admit_ID = pa.Admit_ID AND soc.Status = 0 AND soc.F25 = 1 AND soc.F26 LIKE '1%' AND soc.Record_Created_By IN ( ) ) AND py.Biller_Type IN ( ) AND py.Pay_ID IN ( ) Group by #groupby# Order by #orderby#