SELECT patient.Last_Name_M0040, patient.First_Name_M0040,
ScheduleItems.ScheduleItems_ID, ScheduleItems.HCPCS, ScheduleItems.Total_EClaim, ScheduleItems.Assessment_Tracker_ID,
Assessment_New.Assessment_Reason_M0100, Assessment_New.Certification_Start, Assessment_New.Certification_End
FROM #Request.prefix_db_agency#.ScheduleItems, #Request.prefix_db_agency#.patient, #Request.prefix_db_agency#.Payer, #Request.prefix_db_agency#.Assessment_New
WHERE ScheduleItems.Agency_ID = #session.AgencyID#
AND ScheduleItems.EClaimed = 0
AND ScheduleItems.Deleted <> 1
AND patient.Patient_ID = ScheduleItems.Patient_ID
AND ScheduleItems.Payer_ID = Payer.Payer_ID
AND Payer.Output_Type = 'Medicare'
AND Assessment_New.Certification_end < NOW()
AND Assessment_New.Deleted <> 1
AND Assessment_New.Assessment_Tracker_ID = ScheduleItems.Assessment_Tracker_ID
AND (Assessment_New.Assessment_Reason_M0100 like '1%' or Assessment_New.Assessment_Reason_M0100 like '4%')
AND Assessment_New.Certification_Start <> '0000-00-00'
AND Assessment_New.Certification_End <> '0000-00-00'
Group by ScheduleItems.Assessment_Tracker_ID
ORDER BY Last_Name_M0040