DELETE FROM #Request.prefix_db_agency#.pUnstable AND pg.severity > 1 AND pg.Type IN ('problem','vital') AND pg.Schedule_ID = pSchedules.Schedule_ID AND pg.Description NOT LIKE 'GG%') AS unstable_problems, (pAuths.Total_Visits - pSchedules.Visit_no) AS remaining_visits, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - SN%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_SNdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - PT%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_PTdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - OT%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_OTdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - ST%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_STdischarge, pPayer.Pay_Name FROM #Request.prefix_db_agency#.pProgress JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = pProgress.Schedule_ID JOIN #Request.prefix_db_agency#.pAuths ON pAuths.Auth_ID = pSchedules.Auth_ID AND pAuths.Status = 0 JOIN #Request.prefix_db_agency#.pAssessments ON pSchedules.Assmt_ID = pAssessments.Assmt_ID JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status =0 AND pPtPayer.Payer_Responsibility = 'P - Primary' JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 WHERE pAssessments.status = 0 AND pPatients.Status = 0 AND pSchedules.pgnotesdraft= 2 AND pSchedules.status= 0 AND pSchedules.Emp_ID = '#session.employeeid#' AND pSchedules.Emp_ID != 0 AND pSchedules.Missed = '0' AND pSchedules.Skill IN ('SN','PT','OT') AND pProgress.status= 0 AND pProgress.Type IN ('problem','vital', 'symptom') GROUP BY pSchedules.Skill, pPatients.Patient_ID ORDER BY pSchedules.Skill ASC,pPatients.Patient_ID ASC, pSchedules.Visit_Date DESC UPDATE #Request.prefix_db_agency#.pSchedules s JOIN ( SELECT s.Schedule_ID, ROW_NUMBER() OVER ( PARTITION BY a.Admit_ID, s.Skill ORDER BY s.Visit_Date ASC, s.Schedule_ID ASC ) AS rn FROM #Request.prefix_db_agency#.pSchedules s JOIN #Request.prefix_db_agency#.pAssessments a ON a.Status = 0 AND a.Assmt_ID = s.Assmt_ID JOIN #Request.prefix_db_agency#.pAdmit ad ON ad.Status = 0 AND ad.Admit_ID = a.Admit_ID WHERE s.Status = 0 AND s.Missed = 0 AND s.Visit_Type NOT IN ('1 - SOC - SN', '1 - SOC - PT', '1 - SOC - OT', '3 - ROC - SN', '3 - ROC - PT', 'PT Eval', 'OT Eval', 'ST Eval', 'SN Eval', 'MSW Eval') ) x ON x.Schedule_ID = s.Schedule_ID SET s.Visit_no = x.rn WHERE s.Status = 0 AND s.Missed = 0 AND s.Visit_Type NOT IN ('1 - SOC - SN', '1 - SOC - PT', '1 - SOC - OT', '3 - ROC - SN', '3 - ROC - PT', 'PT Eval', 'OT Eval', 'ST Eval', 'SN Eval', 'MSW Eval') UPDATE #Request.prefix_db_agency#.pSchedules s SET s.Visit_no = 0 WHERE s.Status = 0 AND s.Missed = 0 AND s.Visit_Type IN ('1 - SOC - SN', '1 - SOC - PT', '1 - SOC - OT', '3 - ROC - SN', '3 - ROC - PT', 'PT Eval', 'OT Eval', 'ST Eval', 'SN Eval', 'MSW Eval') UPDATE #Request.prefix_db_agency#.pSchedules s SET s.Visit_no = 0 WHERE s.Status = 0 AND s.Missed = 1 SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pSchedules.Schedule_ID, pSchedules.Visit_Date,pAssessments.Admit_ID, pSchedules.Skill AS Type, pSchedules.Missed,pSchedules.Skill, pSchedules.Progress_Note, pSchedules.Pay_ID, pEmployee.Emp_ID, pEmployee.Emp_Last, pEmployee.Emp_First, pAssessments.Assmt_ID,pAssessments.F26, pProgress.Progress_ID,pProgress.Date_Create,pProgress.Date_Change, IFNULL(pProgress.Severity,0) AS pg_severity,pProgress.Physician_alert, pProgress.Item_ID,pProgress.Description, (SELECT count(*) FROM #Request.prefix_db_agency#.pProgress pg WHERE pg.status= 0 AND pg.severity > 1 AND pg.Type IN ('problem','vital') AND pg.Schedule_ID = pSchedules.Schedule_ID AND pg.Description NOT LIKE 'GG%') AS unstable_problems, ((SELECT SUM(Total_Visits) FROM #Request.prefix_db_agency#.pAuths WHERE Status = 0 AND Skill = pSchedules.Skill AND Patient_ID = pSchedules.Patient_ID) - pSchedules.Visit_no) AS remaining_visits, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - SN%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_SNdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - PT%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_PTdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - OT%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_OTdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = pPatients.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - ST%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS comm_STdischarge, pPayer.Pay_Name FROM #Request.prefix_db_agency#.pProgress JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = pProgress.Schedule_ID JOIN #Request.prefix_db_agency#.pAuths ON pAuths.Auth_ID = pSchedules.Auth_ID AND pAuths.Status = 0 JOIN #Request.prefix_db_agency#.pAssessments ON pSchedules.Assmt_ID = pAssessments.Assmt_ID JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status =0 AND pPtPayer.Payer_Responsibility = 'P - Primary' JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 WHERE pAssessments.status = 0 AND pPatients.Status = 0 AND pSchedules.pgnotesdraft= 2 AND pSchedules.status= 0 AND pSchedules.Emp_ID = '#session.employeeid#' AND pSchedules.Emp_ID != 0 AND pSchedules.Missed = '0' AND pSchedules.Skill IN ('SN','PT','OT') AND pProgress.status= 0 AND pProgress.Type IN ('problem','vital', 'symptom') GROUP BY pSchedules.Skill, pPatients.Patient_ID ORDER BY pSchedules.Skill ASC,pPatients.Patient_ID ASC, pSchedules.Visit_Date DESC ---> INSERT INTO #Request.prefix_db_agency#.pUnstable ( Patient_ID, PatientLast, PatientFirst, Schedule_ID, Visit_Date, Type, Missed, Skill, Progress_Note, Pay_ID, Emp_ID, Emp_Last, Emp_First, Assmt_ID, F26, Progress_ID, Date_Create, Date_Change, Severity, Physician_alert, Item_ID, Description, unstable_problems,remaining_visits,Pay_Name) SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Skill AS Type, pSchedules.Missed, pSchedules.Skill, pSchedules.Progress_Note, pSchedules.Pay_ID, pEmployee.Emp_ID, pEmployee.Emp_Last, pEmployee.Emp_First, pAssessments.Assmt_ID, pAssessments.F26, pProgress.Progress_ID, pProgress.Date_Create, pProgress.Date_Change, IFNULL(pProgress.Severity,0) AS Severity, pProgress.Physician_alert, pProgress.Item_ID, pProgress.Description, IFNULL(uc.unstable_problems,0) AS unstable_problems, ((SELECT SUM(Total_Visits) FROM #Request.prefix_db_agency#.pAuths WHERE Status = 0 AND Skill = pSchedules.Skill AND Patient_ID = pSchedules.Patient_ID) - (SELECT COUNT(Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules ps WHERE ps.Status = 0 AND ps.Skill = pSchedules.Skill AND ps.Patient_ID = pSchedules.Patient_ID AND ps.pgnotesdraft = 2 AND ps.Emp_ID != 0 AND ps.Missed = 0 AND ps.Skill IN ('SN','PT','OT'))) AS remaining_visits, pPayer.Pay_Name FROM #Request.prefix_db_agency#.pProgress pProgress JOIN #Request.prefix_db_agency#.pSchedules pSchedules ON pSchedules.Schedule_ID = pProgress.Schedule_ID JOIN #Request.prefix_db_agency#.pAuths pAuths ON pAuths.Auth_ID = pSchedules.Auth_ID AND pAuths.Status = 0 JOIN #Request.prefix_db_agency#.pAssessments pAssessments ON pSchedules.Assmt_ID = pAssessments.Assmt_ID JOIN #Request.prefix_db_agency#.pPatients pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_lookup#.pEmployee pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID JOIN #Request.prefix_db_agency#.pPtPayer pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status =0 JOIN #Request.prefix_db_agency#.pPayer pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 LEFT JOIN ( SELECT pg.Schedule_ID, COUNT(*) AS unstable_problems FROM #Request.prefix_db_agency#.pProgress pg WHERE pg.status= 0 AND pg.severity > 1 AND pg.Type IN ('problem','vital') AND pg.Description NOT LIKE 'GG%' GROUP BY pg.Schedule_ID ) uc ON uc.Schedule_ID = pSchedules.Schedule_ID LEFT JOIN ( SELECT s.Schedule_ID, SUM(CASE WHEN (cs.Subject LIKE 'Discharge Summary - SN%' OR cs.Subject = 'Agency Transfer/Discharge Summary') AND c.Comm_record_deleted <> 1 AND c.Status = 0 AND c.Comm_date >= s.Visit_Date THEN 1 ELSE 0 END) AS comm_SNdischarge, SUM(CASE WHEN (cs.Subject LIKE 'Discharge Summary - PT%' OR cs.Subject = 'Agency Transfer/Discharge Summary') AND c.Comm_record_deleted <> 1 AND c.Status = 0 AND c.Comm_date >= s.Visit_Date THEN 1 ELSE 0 END) AS comm_PTdischarge, SUM(CASE WHEN (cs.Subject LIKE 'Discharge Summary - OT%' OR cs.Subject = 'Agency Transfer/Discharge Summary') AND c.Comm_record_deleted <> 1 AND c.Status = 0 AND c.Comm_date >= s.Visit_Date THEN 1 ELSE 0 END) AS comm_OTdischarge, SUM(CASE WHEN (cs.Subject LIKE 'Discharge Summary - ST%' OR cs.Subject = 'Agency Transfer/Discharge Summary') AND c.Comm_record_deleted <> 1 AND c.Status = 0 AND c.Comm_date >= s.Visit_Date THEN 1 ELSE 0 END) AS comm_STdischarge FROM #Request.prefix_db_agency#.pSchedules s JOIN #Request.prefix_db_agency#.Communication c ON c.Patient_ID = s.Patient_ID JOIN #Request.prefix_db_agency#.CommunicationSubjects cs ON cs.CommSub_ID = c.CommSub_ID GROUP BY s.Schedule_ID ) cc ON cc.Schedule_ID = pSchedules.Schedule_ID WHERE pAssessments.status = 0 AND pPatients.Status = 0 AND pSchedules.pgnotesdraft= 2 AND pSchedules.status= 0 AND pSchedules.Emp_ID = '#session.employeeid#' AND pSchedules.Emp_ID != 0 AND pSchedules.Missed = '0' AND pSchedules.Skill IN ('SN','PT','OT') AND pProgress.status= 0 AND pProgress.Type IN ('problem','vital', 'symptom') AND ( (pSchedules.Skill = 'SN' AND IFNULL(cc.comm_SNdischarge,0) = 0) OR (pSchedules.Skill = 'PT' AND IFNULL(cc.comm_PTdischarge,0) = 0) OR (pSchedules.Skill = 'OT' AND IFNULL(cc.comm_OTdischarge,0) = 0) OR (pSchedules.Skill = 'ST' AND IFNULL(cc.comm_STdischarge,0) = 0) ) GROUP BY pSchedules.Skill, pPatients.Patient_ID