SELECT exp_assmt.Assmt_ID, exp_assmt.Patient_ID, exp_assmt.Record_creation_Date, exp_assmt.admission_status, exp_assmt.Assessment_Reason_M0100, exp_assmt.Certification_Start, exp_assmt.Certification_End, exp_assmt.Assessment_Completed_M0090, exp_assmt.Transmission_status, exp_assmt.Expiration_Date, pPatients.Pt_First, pPatients.Pt_Last, pPatients.Pt_Middle, pEmployee.Emp_Last, pEmployee.Emp_First, COALESCE(NULLIF(pAdmit.Case_Manager, 0), 2) AS AssignedEmpID FROM ( SELECT latest_assmt.Admit_ID, latest_assmt.Assmt_ID, latest_assmt.Patient_ID, latest_assmt.Record_Creation_Date, latest_assmt.F27, pAssessments.F25 AS admission_status, pAssessments.F26 AS Assessment_Reason_M0100, pAssessments.F177 AS Certification_Start, pAssessments.F178 AS Certification_End, pAssessments.F176 AS Assessment_Completed_M0090, pAssessments.F197 AS Transmission_status, pAssessments.F178 AS Expiration_Date FROM ( SELECT pAssessments.Admit_ID, pAssessments.Assmt_ID, pAssessments.Patient_ID, pAssessments.F178, pAssessments.F27, pAssessments.Record_Creation_Date FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Status = 0 WHERE pAssessments.Status = 0 AND (LEFT(pAssessments.F26, 1) = '1' OR LEFT(pAssessments.F26, 1) = '4') AND pAssessments.F178 IN ( SELECT MAX(ass.F178) FROM #Request.prefix_db_agency#.pAssessments ass WHERE ass.Admit_ID = pAssessments.Admit_ID AND ass.Status = 0 AND (LEFT(ass.F26, 1) = '1' OR LEFT(ass.F26, 1) = '4') ) ) AS latest_assmt JOIN #Request.prefix_db_agency#.pAssessments pAssessments ON pAssessments.Assmt_ID = latest_assmt.Assmt_ID AND pAssessments.Status = 0 UNION ALL SELECT pAssessments.Admit_ID, pAssessments.Assmt_ID, pAssessments.Patient_ID, pAssessments.Record_Creation_Date, pAssessments.F27, pAssessments.F25 AS admission_status, pAssessments.F26 AS Assessment_Reason_M0100, pAssessments.F177 AS Certification_Start, pAssessments.F178 AS Certification_End, pAssessments.F176 AS Assessment_Completed_M0090, pAssessments.F197 AS Transmission_status, DATE_ADD(pAssessments.F176, INTERVAL 30 DAY) AS Expiration_Date FROM #Request.prefix_db_agency#.pAssessments pAssessments JOIN #Request.prefix_db_agency#.pAdmit pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Status = 0 WHERE pAssessments.Status = 0 AND pAssessments.F176 IS NOT NULL AND pAssessments.F176 > '1900-01-01' AND LOWER(pAssessments.F26) LIKE '%eval%' ) AS exp_assmt JOIN #Request.prefix_db_agency#.pAdmit pAdmit ON pAdmit.Admit_ID = exp_assmt.Admit_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_agency#.pPatients pPatients ON pPatients.Patient_ID = exp_assmt.Patient_ID AND pPatients.Status = 0 LEFT JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager AND pEmployee.Status = 0 WHERE pPatients.Loc_ID = '#session.agencyid#' AND exp_assmt.admission_status = 1 AND ( ( (LEFT(exp_assmt.Assessment_Reason_M0100, 1) = '1' OR LEFT(exp_assmt.Assessment_Reason_M0100, 1) = '4') AND DATE_SUB(exp_assmt.Certification_End, INTERVAL 14 DAY) <= CURDATE() ) OR ( LOWER(exp_assmt.Assessment_Reason_M0100) LIKE '%eval%' AND DATE_ADD(exp_assmt.Assessment_Completed_M0090, INTERVAL 30 DAY) <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) ) ) AND exp_assmt.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 NOT EXISTS ( SELECT 1 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 ( (LEFT(exp_assmt.F27, 2) = 'SN' AND CommunicationSubjects.Subject LIKE 'Discharge Summary - SN%') OR (LEFT(exp_assmt.F27, 2) = 'PT' AND CommunicationSubjects.Subject LIKE 'Discharge Summary - PT%') OR (LEFT(exp_assmt.F27, 2) = 'OT' AND CommunicationSubjects.Subject LIKE 'Discharge Summary - OT%') OR (LEFT(exp_assmt.F27, 2) = 'ST' AND CommunicationSubjects.Subject LIKE 'Discharge Summary - ST%') OR (LEFT(exp_assmt.F27, 3) = 'MSW' AND CommunicationSubjects.Subject LIKE 'Discharge Summary - MSW%') OR CommunicationSubjects.Subject LIKE '%Agency Transfer%' ) ) AND exp_assmt.Expiration_Date BETWEEN AND AND pAdmit.Case_Manager = ORDER BY exp_assmt.Expiration_Date ASC
Search Expiring Assessment List
Fetch latest report Assessments by Case Manager Rollover Assessments Send a Communication
" /> to " />
Date Format: mm/dd/yyyy
Expiring Assessment List
Expiration Date Cert. Start Completed Date Patient Name M0100 Assessment Reason Status Expiring
Days
#DateFormat(Expiration_Date, "mm/dd/yy")# #DateFormat(Certification_Start, "mm/dd/yy")# #DateFormat(Assessment_Completed_M0090, "mm/dd/yy")# #Pt_Last#, #Pt_First# #Assessment_Reason_M0100# CompleteIn Progress #diffdays#
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 ( , , , , , )