SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAdmit.*, COALESCE(NULLIF(pAdmit.Case_Manager, 0), 2) AS AssignedEmpID, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAssessments.Patient_ID = pPatients.Patient_ID AND pAssessments.Status = 0 AND Left(pAssessments.F26,1) = '1') AS SOC_count, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAssessments.Patient_ID = pPatients.Patient_ID AND pAssessments.Status = 0 AND Left(pAssessments.F26,1) = '4') AS Recert_count , (SELECT count(Communication.Comm_ID) FROM #Request.prefix_db_agency#.Communication JOIN #Request.prefix_db_agency#.CommunicationSubjects ON CommunicationSubjects.CommSub_ID = Communication.CommSub_ID WHERE Communication.Status = 0 AND Communication.Patient_ID = pPatients.Patient_ID AND pAdmit.M0030_START_CARE_DT < Communication.Comm_Date AND (CommunicationSubjects.Subject like "Discharge Summary%" OR CommunicationSubjects.Subject like '%Agency Transfer%')) AS communicationsubject_count, (SELECT F178 FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAssessments.Patient_ID = pPatients.Patient_ID AND pAssessments.Status = 0 AND Left(pAssessments.F26,1) IN ('4','1') ORDER BY F178 DESC LIMIT 0,1) AS Certification_End FROM #Request.prefix_db_agency#.pAdmit JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAdmit.Patient_ID JOIN #Request.prefix_db_agency#.pAssessments ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAssessments.Patient_ID = pPatients.Patient_ID WHERE pPatients.Status = 0 And pAdmit.Status = 0 AND pAdmit.Admit_ID NOT IN (SELECT pAssessments.Admit_ID FROM #Request.prefix_db_agency#.pAssessments WHERE (pAssessments.F26 like '9%' OR pAssessments.F26 like '7%' OR pAssessments.F26 like '8%') AND pAssessments.Status =0 AND pAssessments.F25 = 1) AND pPatients.Patient_ID IN (SELECT pSchedules.Patient_ID FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID WHERE pSchedules.Emp_ID = AND pSchedules.Status = 0 AND pAssessments.Status =0 AND pSchedules.Followup_visit = 1 ) AND COALESCE(NULLIF(pAdmit.Case_Manager, 0), 2) = GROUP BY pAdmit.Admit_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT pPatients.Patient_ID as pat_id, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, CASE WHEN LEFT(pAssessments.F26,1) = '4' AND DATEDIFF(pAssessments.F178, now()) <= 1 THEN 1 ELSE 0 END AS Recert_need , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments ass WHERE LEFT(ass.F26,1) = '4' AND DATEDIFF(ass.F178, now()) >= 1 AND ass.Patient_ID = pAssessments.Patient_ID ) AS Recert_exisiting, CASE WHEN (pAssessments.F26 = 'PT Adult Evaluation' OR pAssessments.F26 = 'PT Followup') AND DATE_ADD(pAssessments.F176, INTERVAL 30 DAY) < now() THEN 1 ELSE 0 END AS PT_need , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments ass WHERE ass.F26 = 'PT Followup' AND ass.Patient_ID = pAssessments.Patient_ID AND ass.Admit_ID = pAssessments.Admit_ID) AS PT_Followup, (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 IN ('Discharge Summary - PT') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS PT_summary, CASE WHEN (pAssessments.F26 = 'OT Adult Evaluation' OR pAssessments.F26 = 'OT Followup') AND DATE_ADD(pAssessments.F176, INTERVAL 30 DAY) < now() THEN 1 ELSE 0 END AS OT_need , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments ass WHERE ass.F26 = 'OT Followup' AND ass.Patient_ID = pAssessments.Patient_ID AND ass.Admit_ID = pAssessments.Admit_ID) AS OT_Followup, (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 IN ('Discharge Summary - OT') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS OT_summary, CASE WHEN (pAssessments.F26 = 'ST Adult Evaluation' OR pAssessments.F26 = 'ST Followup') AND DATE_ADD(pAssessments.F176, INTERVAL 30 DAY) < now() THEN 1 ELSE 0 END AS ST_need , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments ass WHERE ass.F26 = 'ST Followup' AND ass.Patient_ID = pAssessments.Patient_ID AND ass.Admit_ID = pAssessments.Admit_ID) AS ST_Followup, (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 IN ('Discharge Summary - ST') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS ST_summary, CASE WHEN (pAssessments.F26 = 'MSW Evaluation' OR pAssessments.F26 = 'MSW Followup') AND DATE_ADD(pAssessments.F176, INTERVAL 30 DAY) < now()THEN 1 ELSE 0 END AS MSW_need , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments ass WHERE ass.F26 = 'MSW Followup' AND ass.Patient_ID = pAssessments.Patient_ID AND ass.Admit_ID = pAssessments.Admit_ID) AS MSW_Followup, (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 IN ('Discharge Summary - MSW') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS MSW_summary, (SELECT count(Communication.Comm_ID) FROM #Request.prefix_db_agency#.Communication JOIN #Request.prefix_db_agency#.CommunicationSubjects ON CommunicationSubjects.CommSub_ID = Communication.CommSub_ID WHERE Communication.Status = 0 AND Communication.Patient_ID = pPatients.Patient_ID AND pAdmit.M0030_START_CARE_DT < Communication.Comm_Date AND (CommunicationSubjects.Subject like pAssessments.F27 OR CommunicationSubjects.Subject like "Discharge Summary%" OR CommunicationSubjects.Subject like '%Agency Transfer%')) AS communicationsubject_count, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pa WHERE pa.Patient_ID = pAssessments.Patient_ID and pAdmit.Admit_ID = pa.Admit_ID AND pa.Status = 0 AND pa.F25 = 1 AND LEFT(pa.F26,1) IN ('7','8','9')) AS dischargeoasis_count, (SELECT count(*) FROM #Request.prefix_db_agency#.pAuths pa WHERE pa.Patient_ID = pAssessments.Patient_ID AND pa.Skill = 'PT' AND pa.Assmt_ID = 0 AND pa.Status = 0 ) AS pt_noauth, (SELECT count(*) FROM #Request.prefix_db_agency#.pAuths pa WHERE pa.Patient_ID = pAssessments.Patient_ID AND pa.Skill = 'OT' AND pa.Assmt_ID = 0 AND pa.Status = 0 ) AS ot_noauth, (SELECT count(*) FROM #Request.prefix_db_agency#.pAuths pa WHERE pa.Patient_ID = pAssessments.Patient_ID AND pa.Skill = 'ST' AND pa.Assmt_ID = 0 AND pa.Status = 0 ) AS st_noauth, (SELECT count(*) FROM #Request.prefix_db_agency#.pAuths pa WHERE pa.Patient_ID = pAssessments.Patient_ID AND pa.Skill = 'MSW' AND pa.Assmt_ID = 0 AND pa.Status = 0 ) AS msw_noauth, COALESCE(NULLIF(pAdmit.Case_Manager, 0), 2) AS AssignedEmpID, pAssessments.* FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAdmit.Patient_ID WHERE pAdmit.Status = 0 AND pPatients.status = 0 AND pAssessments.status = 0 AND pAdmit.Admit_ID NOT IN (SELECT pAssessments.Admit_ID FROM #Request.prefix_db_agency#.pAssessments WHERE (pAssessments.F26 like '9%' OR pAssessments.F26 like '7%' OR pAssessments.F26 like '8%') AND pAssessments.Status =0 AND pAssessments.F25 = 1) AND pPatients.Patient_ID IN (SELECT pSchedules.Patient_ID FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID WHERE pSchedules.Emp_ID = AND pSchedules.Status = 0 AND pAssessments.Status =0 ) AND COALESCE(NULLIF(pAdmit.Case_Manager, 0), 2) = GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC, pAssessments.F178 DESC, pAssessments.F176 DESC SELECT * FROM getdischarge WHERE dischargeoasis_count = 0 AND (Recert_need = 1 OR (PT_need = 1 AND PT_summary= 0 AND pt_noauth = 0 AND PT_Followup = 0) OR (OT_need = 1 AND OT_summary= 0 AND ot_noauth = 0 AND OT_Followup = 0) OR (ST_need = 1 AND ST_summary= 0 AND st_noauth = 0 AND ST_Followup = 0) OR (MSW_need = 1 AND MSW_summary= 0 AND msw_noauth = 0 AND MSW_Followup = 0)) AND communicationsubject_count = 0
Missing OASIS/Evaluations
Fetch latest report
Patient Missing OASIS Cert End OASIS Completion date Days Elapsed Create
#PatientLast# #PatientFirst# Get List YesIncomplete Yes #DateFormat(Certification_End,"mm/dd/yyyy")# #diffdays# Create
#PatientLast#, #PatientFirst# Need OASIS #assessment_reason#
#DateFormat(F178, 'mm/dd/yyyy')#
 #DateFormat(F176, 'mm/dd/yyyy')# #diffdays# Create
UPDATE #Request.prefix_db_agency#.Dash_emp_count SET status = 1 WHERE status = 0 AND Header = AND Emp_ID = INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , ) UPDATE #Request.prefix_db_agency#.Dash_emp_count SET status = 1 WHERE status = 0 AND Header = INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , )