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 AND ScheduleItems.Deleted = 0 ) AS FirstVisitDate,'1' as RAP, (SELECT ScheduleItems_ID FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start AND ScheduleItems.Deleted = 0 ORDER BY ScheduleItems.Visit_Date ASC LIMIT 1 ) AS Recert_ScheduleItems_ID, (SELECT '1' FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start AND ScheduleItems.Invoice_ID !=0 AND ScheduleItems.Deleted = 0 LIMIT 1 ) AS bill, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 THEN Assessment_New.Certification_Start WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 THEN DATE_ADD(Assessment_New.Certification_Start, INTERVAL 31 DAY) ELSE Assessment_New.Certification_Start END as start_date, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 THEN DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 THEN Assessment_New.Certification_End ELSE Assessment_New.Certification_End END as end_date, 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 JOIN #Request.prefix_db_agency#.patient ON patient.Patient_ID = ScheduleItems.Patient_ID JOIN #Request.prefix_db_lookup#.Employee ON ScheduleItems.Employee_ID = Employee.Employee_ID JOIN #Request.prefix_db_agency#.Payer ON ScheduleItems.Payer_ID = Payer.Payer_ID JOIN #Request.prefix_db_agency#.Assessment_New ON Assessment_New.Assessment_Tracker_ID = ScheduleItems.Assessment_Tracker_ID WHERE ScheduleItems.Agency_ID = '#session.AgencyID#' AND ScheduleItems.Payer_ID = '#url.ID#' AND ScheduleItems.Output_Type in ('RAP','Medicare') AND patient.Status != 'deleted' AND patient.Primary_ReferringPhys_ID_M0072 != 0 AND Assessment_New.Admission_Status = 'Complete' AND ScheduleItems.Deleted = 0 AND ScheduleItems.Missed = 'No' AND ScheduleItems.EClaimed = 0 AND ScheduleItems.Invoice_ID =0 AND ScheduleItems.Nonbillable = 0 AND Assessment_New.Deleted = 0 AND ( Assessment_New.Transmission_Status like '%transmitted%' OR Assessment_New.Assessment_Reason_M0100 = 'Hospice Comprehensive' ) AND Assessment_New.Certification_Start < GROUP BY Assessment_Tracker_ID UNION ALL 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 AND ScheduleItems.Deleted = 0 ) AS FirstVisitDate,'1' as RAP, (SELECT ScheduleItems_ID FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start AND ScheduleItems.Deleted = 0 ORDER BY ScheduleItems.Visit_Date ASC LIMIT 1 ) AS Recert_ScheduleItems_ID, (SELECT '1' FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id -- AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start AND DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) < 30 AND ScheduleItems.Invoice_ID !=0 AND ScheduleItems.Deleted = 0 LIMIT 1 ) AS bill, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 THEN Assessment_New.Certification_Start WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 THEN DATE_ADD(Assessment_New.Certification_Start, INTERVAL 31 DAY) ELSE Assessment_New.Certification_Start END as start_date, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 THEN DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 THEN Assessment_New.Certification_End ELSE Assessment_New.Certification_End END as end_date, 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 JOIN #Request.prefix_db_agency#.patient ON patient.Patient_ID = ScheduleItems.Patient_ID JOIN #Request.prefix_db_lookup#.Employee ON ScheduleItems.Employee_ID = Employee.Employee_ID JOIN #Request.prefix_db_agency#.Payer ON ScheduleItems.Payer_ID = Payer.Payer_ID JOIN #Request.prefix_db_agency#.Assessment_New ON Assessment_New.Assessment_Tracker_ID = ScheduleItems.Assessment_Tracker_ID WHERE ScheduleItems.Agency_ID = '#session.AgencyID#' AND ScheduleItems.Payer_ID = '#url.ID#' AND ScheduleItems.Output_Type in ('RAP','Medicare') AND patient.Status != 'deleted' AND patient.Primary_ReferringPhys_ID_M0072 != 0 AND Assessment_New.Admission_Status = 'Complete' AND ScheduleItems.Deleted = 0 AND ScheduleItems.Missed = 'No' AND ScheduleItems.EClaimed = 0 AND ScheduleItems.Invoice_ID =0 AND ScheduleItems.Nonbillable = 0 AND Assessment_New.Deleted = 0 AND ( Assessment_New.Transmission_Status like '%transmitted%' OR Assessment_New.Assessment_Reason_M0100 = 'Hospice Comprehensive' ) AND Assessment_New.Certification_Start >= -- AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start AND ScheduleItems.Visit_Date < DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) GROUP BY Assessment_Tracker_ID UNION ALL 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 >= DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) AND ScheduleItems.Deleted = 0 ) AS FirstVisitDate,'2' as RAP, (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) AND ScheduleItems.Deleted = 0 ORDER BY ScheduleItems.Visit_Date ASC LIMIT 1 ) AS Recert_ScheduleItems_ID, (SELECT '1' FROM #Request.prefix_db_agency#.ScheduleItems WHERE ScheduleItems.Assessment_Tracker_Id = Assessment_New.Assessment_Tracker_Id AND ScheduleItems.Visit_Date >= Assessment_New.Certification_Start AND DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) >= 30 AND ScheduleItems.Invoice_ID !=0 AND ScheduleItems.Deleted = 0 LIMIT 1 ) AS bill, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 THEN Assessment_New.Certification_Start WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 THEN DATE_ADD(Assessment_New.Certification_Start, INTERVAL 31 DAY) ELSE Assessment_New.Certification_Start END as start_date, CASE WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) <= 30 THEN DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) WHEN DATEDIFF(ScheduleItems.Visit_Date,Assessment_New.Certification_Start) > 30 THEN Assessment_New.Certification_End ELSE Assessment_New.Certification_End END as end_date, 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 JOIN #Request.prefix_db_agency#.patient ON patient.Patient_ID = ScheduleItems.Patient_ID JOIN #Request.prefix_db_lookup#.Employee ON ScheduleItems.Employee_ID = Employee.Employee_ID JOIN #Request.prefix_db_agency#.Payer ON ScheduleItems.Payer_ID = Payer.Payer_ID JOIN #Request.prefix_db_agency#.Assessment_New ON Assessment_New.Assessment_Tracker_ID = ScheduleItems.Assessment_Tracker_ID WHERE ScheduleItems.Agency_ID = '#session.AgencyID#' AND ScheduleItems.Payer_ID = '#url.ID#' AND ScheduleItems.Output_Type in ('RAP','Medicare') AND patient.Status != 'deleted' AND patient.Primary_ReferringPhys_ID_M0072 != 0 AND Assessment_New.Admission_Status = 'Complete' AND ScheduleItems.Deleted = 0 AND ScheduleItems.Missed = 'No' AND ScheduleItems.EClaimed = 0 AND ScheduleItems.Invoice_ID =0 AND ScheduleItems.Nonbillable = 0 AND Assessment_New.Deleted = 0 AND ( Assessment_New.Transmission_Status like '%transmitted%' OR Assessment_New.Assessment_Reason_M0100 = 'Hospice Comprehensive' ) AND Assessment_New.Certification_Start >= AND ScheduleItems.Visit_Date >= DATE_ADD(Assessment_New.Certification_Start, INTERVAL 30 DAY) AND Assessment_New.Certification_Start <> '0000-00-00' AND Assessment_New.Certification_End <> '0000-00-00' GROUP BY Assessment_Tracker_ID ORDER BY Last_Name_M0040, First_Name_M0040,Assessment_Tracker_ID, Visit_Date