SELECT patient.Agency_ID AS Agency, Assessment_New.Assessment_Tracker_ID,
Assessment_New.Certification_Start AS CertStart,
Assessment_New.Certification_End AS CertEnd, Assessment_New.Patient_ID,
patient.Patient_ID, patient.Last_Name_M0040 AS PatientLast, patient.First_Name_M0040 AS PatientFirst,
ScheduleItems.Assessment_Tracker_ID, ScheduleItems.ScheduleItems_ID, ScheduleItems.Visit_Date AS Date, ScheduleItems.Visit_Type AS Type, ScheduleItems.Bill_Type AS Bill
FROM #Request.prefix_db_agency#.Assessment_New, #Request.prefix_db_agency#.patient, #Request.prefix_db_agency#.ScheduleItems
WHERE ScheduleItems.Assessment_Tracker_ID = Assessment_New.Assessment_Tracker_ID
AND patient.Patient_ID = ScheduleItems.Patient_ID
AND patient.Last_Name_M0040 LIKE '#URL.Alpha#%'
AND Assessment_New.Certification_Start <> '0000-00-00'
AND Assessment_New.Certification_End <> '0000-00-00'
AND ScheduleItems.Visit_Date <> '0000-00-00'
AND patient.Status = 'active'
AND patient.Agency_ID = #session.AgencyID#
AND Assessment_New.Certification_Start <> ''
AND ScheduleItems.EClaimed = 1
ORDER BY Last_Name_M0040, Certification_Start ASC, Visit_Date ASC