SELECT Distinct(patient.Patient_ID), Assessment_New.Certification_Start,Assessment_New.Certification_End, Assessment_New.Assessment_Reason_M0100,
patient.Agency_ID, patient.Last_Name_M0040 AS PatientLast, patient.First_Name_M0040 AS PatientFirst, patient.Status
FROM #Request.prefix_db_agency#.patient, #Request.prefix_db_agency#.Assessment_New, #Request.prefix_db_lookup#.Agency
WHERE NOT EXISTS (SELECT Assessment_Forms.Patient_ID , Assessment_New.Assessment_ID
FROM #Request.prefix_db_agency#.Assessment_Forms
WHERE Assessment_Forms.Patient_ID = patient.Patient_ID
AND Assessment_Forms.Form_Type = 'Form 485'
AND Assessment_Forms.Form_Status != 'Delete')
AND Assessment_New.Patient_ID = patient.Patient_ID
AND Assessment_New.Deleted = 0
AND Assessment_New.Certification_End > Now()
AND Assessment_New.Assessment_Reason_M0100 != 'NO ASSESSMENT'
AND patient.Agency_ID = Agency.Agency_ID
and patient.Status = 'active'
AND
(
patient.Employee_ID = '
#Session.EmployeeID#'
OR
'#session.EmployeeId#'
IN (
select distinct employee_id from #Request.prefix_db_agency#.ScheduleItems where patient_id = patient.Patient_Id and deleted <> 1
)
)
ORDER BY patient.Last_Name_M0040 ASC
AND Assessment_Forms.Form_Status <> 'Delete')
AND Assessment_New.Patient_ID = patient.Patient_ID
AND Assessment_New.Assessment_Id is not null
AND Assessment_New.Deleted = 0
AND Assessment_New.Assessment_Reason_M0100 != 'NO ASSESSMENT'
AND patient.Agency_ID = #session.AgencyID#
AND patient.Status = 'active'
AND patient.Last_Name_M0040 not REGEXP BINARY '[A-Z]{2}'
AND patient.First_Name_M0040 not REGEXP BINARY '[A-Z]{2}'
AND
(
patient.Employee_ID = '
#Session.EmployeeID#'
OR
'#session.EmployeeId#'
IN (
select distinct employee_id from #Request.prefix_db_agency#.ScheduleItems where patient_id = patient.Patient_Id and deleted <> 1
)
)
ORDER BY patient.Last_Name_M0040 ASC