SELECT patient.Patient_ID, patient.Last_Name_M0040, patient.First_Name_M0040, patient.Status AS PatStat, patient.Primary_ReferringPhys_ID_M0072 AS DOC,Employee.Last_Name, Employee.First_Name, ScheduleItems.ScheduleItems_ID,ScheduleItems.Payer_ID,ScheduleItems.ApproveBill, ScheduleItems.Nonbillable, ScheduleItems.Visit_Date, (SELECT MIN(Visit_Date) FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start ) AS FirstVisitDate, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 AND ScheduleItems.Invoice_ID = 0 THEN true WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 AND DATEDIFF(now(),Assessment_New.Certification_Start) >= 30 AND ScheduleItems.Invoice_ID2 = 0 THEN true WHEN Assessment_New.Certification_End <= NOW() THEN TRUE ELSE false END as bill, CASE WHEN ScheduleItems.EClaimed = 1 AND Assessment_New.Certification_Start > '2019-12-01' -- AND ScheduleItems.Output_Type = 'RAP' AND ScheduleItems.Invoice_ID2 = 0 THEN 2 WHEN ScheduleItems.EClaimed = 0 -- AND ScheduleItems.Output_Type = 'RAP' AND ScheduleItems.Invoice_ID = 0 THEN 1 ELSE 0 END as RAP_Eligible, (SELECT ScheduleItems_ID FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Visit_Date >= DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) LIMIT 1 ) AS ScheduleItems_ID2, CASE WHEN ScheduleItems.Output_Type = 'RAP' AND Assessment_New.Certification_Start > '2019-12-01' THEN true ELSE false END as RAP_Exists, ScheduleItems.Total_Units, ScheduleItems.Output_Type, ScheduleItems.EClaimed, ScheduleItems.Visit_Type AS Type,ScheduleItems.Employee_ID, Assessment_New.Assessment_Reason_M0100, Assessment_New.Certification_Start, Assessment_New.Transmission_Status AS TRANSTATUS, Assessment_New.Assessment_Reason_M0100 AS Reason, Assessment_New.Admission_Status AS Status,Assessment_New.Assessment_Number, Assessment_New.Assessment_ID, Assessment_New.Certification_End, Assessment_New.HIPPS_Code, Assessment_New.Homebound_Status,Assessment_New.Assessment_Tracker_ID, ScheduleItems.Invoice_ID,ScheduleItems.Invoice_ID2, Payer.Payer_ID, Payer.Payer_ID AS PAYID, Payer.Output_Type as PAYOUT, Payer.PayName,Payer.notes FROM #Request.prefix_db_agency#.ScheduleItems, #Request.prefix_db_agency#.patient, #Request.prefix_db_lookup#.Employee, #Request.prefix_db_agency#.Payer, #Request.prefix_db_agency#.Assessment_New WHERE ScheduleItems.Agency_ID = '#session.AgencyID#' AND ScheduleItems.Payer_ID = '#url.ID#' -- AND ScheduleItems.Output_Type = 'RAP' AND patient.Patient_ID = ScheduleItems.Patient_ID AND patient.Status != 'deleted' AND patient.Primary_ReferringPhys_ID_M0072 != 0 AND Assessment_New.Assessment_Tracker_ID = ScheduleItems.Assessment_Tracker_ID AND Assessment_New.Admission_Status = 'Complete' AND ScheduleItems.Total_Units != 0 AND ScheduleItems.Deleted = 0 AND ScheduleItems.Missed = 'No' -- AND ScheduleItems.EClaimed = 0 AND ScheduleItems.Nonbillable = 0 AND ScheduleItems.Payer_ID = Payer.Payer_ID AND ScheduleItems.Employee_ID = Employee.Employee_ID AND Assessment_New.Deleted = 0 AND ( Assessment_New.Transmission_Status like '%transmitted%' OR Assessment_New.Assessment_Reason_M0100 = 'Hospice Comprehensive' ) AND patient.Patient_ID = AND ScheduleItems.Visit_Date >= AND ScheduleItems.Visit_Date <= AND Assessment_New.Certification_Start <> '0000-00-00' AND Assessment_New.Certification_End <> '0000-00-00' GROUP BY Assessment_New.Assessment_Tracker_ID ORDER BY Last_Name_M0040, First_Name_M0040, Assessment_New.Assessment_Tracker_ID, Visit_Date