SELECT parent_oasis.F26 AS parent_assessment, parent_oasis.F176 AS parent_Assessment_Completed_M0090, pAssessments.Assmt_ID, pAssessments.Record_creation_Date, pAssessments.Assmt_ID, pAssessments.F27,pAssessments.Admit_ID, pAssessments.Patient_ID, 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, pPatients.Pt_First, pPatients.Pt_Last, Pt_Middle, (SELECT count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pCareplan.Assmt_ID = pAssessments.Assmt_ID AND pCareplan.Status = 0 AND pCareplan.Skill = pAssessments.F27) AS careplancount, (SELECT count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pCareplan.Assmt_ID = assessment_assign.Parent_Assmt_ID AND pCareplan.Status = 0 AND pCareplan.Skill = pAssessments.F27) AS notcreatedcareplan, (SELECT count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pCareplan.Assmt_ID = assessment_assign.Parent_Assmt_ID AND pCareplan.Status = 0 AND pCareplan.Skill = pAssessments.F27 AND pCareplan.Type IN ('discharge_plan','patient_goal') AND pCareplan.Description IN ( 'add patient-stated goal','add discharge plan')) AS ptgoalsnotadded, (SELECT count(*) FROM #Request.prefix_db_agency#.pWoundsVitals WHERE Assmt_ID = pAssessments.Assmt_ID AND Status = 0 AND (Description != 'weight' OR Description != 'Weight') AND VitalOrWound = 'vital' AND (Param_Lo IS NULL OR Param_Hi IS NULL OR Param_Lo= '' OR Param_Hi = '')) AS required_vital, (SELECT count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pCareplan.Assmt_ID = assessment_assign.Parent_Assmt_ID AND pCareplan.Status = 0 AND pCareplan.Skill = pAssessments.F27 AND pCareplan.Type IN ('standing_order')) AS standingorders_count, (SELECT count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pCareplan.Assmt_ID = pAssessments.Assmt_ID AND pCareplan.Status = 0 AND pCareplan.Skill = pAssessments.F27 AND pCareplan.Type IN ('discharge_plan','patient_goal') ) AS patientgoalscount, (SELECT count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pCareplan.Assmt_ID IN ( SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments ass WHERE ass.Admit_ID = pAdmit.Admit_ID AND ass.Status =0) AND pCareplan.Status = 0 AND pCareplan.Skill = pAssessments.F27) AS adultcareplancount , (SELECT count(*) FROM #Request.prefix_db_agency#.pPtmeds WHERE pPtmeds.In_Home = 2 AND pPtmeds.Status = 0 AND pPatients.Patient_ID = pPtmeds.Patient_ID) AS incomplete_meds, (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 - PT%") AS PTdischargecount, (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 - OT%") AS OTdischargecount, (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 - ST%") AS STdischargecount, (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 - SN%") AS SNdischargecount, pEmployee.Emp_First,pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID AND assessment_assign.F26 = pAssessments.F26 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = assessment_assign.Emp_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments parent_oasis ON parent_oasis.Assmt_ID = assessment_assign.Parent_Assmt_ID Where pPatients.Loc_ID = '#session.agencyid#' AND pPatients.Status = 0 AND pAssessments.Status = 0 AND pAssessments.F25 = 1 AND (pAssessments.F26 LIKE '1%' OR pAssessments.F26 LIKE '4%' OR pAssessments.F26 LIKE 'PT Adult%' OR pAssessments.F26 LIKE 'OT Adult%' OR pAssessments.F26 LIKE 'ST Adult%' OR pAssessments.F26 LIKE 'SN Adult%' ) AND pEmployee.Emp_ID = #session.EmployeeID# AND pAssessments.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) GROUP BY pAssessments.Assmt_ID
Missing Careplan
Fetch latest report
Cert. End Cert. Start Completed Date Patient Name Clinician M0100 Assessment Reason Status Completed Days Reason
#DateFormat(Certification_End, "mm/dd/yy")# #DateFormat(Certification_Start, "mm/dd/yy")# #DateFormat(Assessment_Completed_M0090, "mm/dd/yy")# #Pt_Last#, #Pt_First# #Emp_Last#, #Emp_First# #Assessment_Reason_M0100# CompleteIn Progress #diffdays# careplan not created Patient goal OR discharge plan not added Incomplete Vital Sign Standing orders required Missing careplan
UPDATE #Request.prefix_db_lookup#.pEmployee SET Emp_missing_careplan = #Emp_missing_careplan_count# WHERE Emp_ID = #session.EmployeeID#