SELECT agency_id, Agency_Login, Agency_Name FROM #Request.prefix_db_lookup#.Agency WHERE status = 'active' AND Agency_ID = ORDER BY agency_id DELETE FROM #Request.prefix_db_agency#.Email_Daily_QA UPDATE #Request.prefix_db_agency#.Email_Daily_QA SET status = 1 WHERE status = 0 SELECT count(pEmployee.Emp_ID) as ecount, pEmployee.Emp_ID as empIdValue, (SELECT Admit_ID FROM #Request.prefix_db_agency#.pAdmit WHERE Patient_ID = pAssessments.Patient_ID AND Status = 0 ORDER BY M0030_START_CARE_DT DESC LIMIT 1) AS latest_admit, FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Status = 0 AND pAdmit.Drafts = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status IN (0) LEFT JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID AND assessment_assign.F26 = pAssessments.F26 LEFT JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = assessment_assign.Emp_ID AND pEmployee.Status = 0 WHERE pAssessments.Status = 0 AND (pAssessments.F25 IS NULL OR pAssessments.F25 = '0') AND pAssessments.F176 < group by Emp_ID INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , ) SELECT count(pEmployee.Emp_ID) as ecount, pEmployee.Emp_ID AS AssignedEmpID, (SELECT Admit_ID FROM #Request.prefix_db_agency#.pAdmit WHERE Patient_ID = pAssessments.Patient_ID AND Status = 0 ORDER BY M0030_START_CARE_DT DESC LIMIT 1) AS latest_admit FROM #Request.prefix_db_agency#.pAssessments pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_agency#.pPatients pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.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 AND pEmployee.Status = 0 JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAssessments.Status = 0 AND (pAssessments.F26 LIKE '1%' OR pAssessments.F26 LIKE '4%') AND pAssessments.F25 = 1 AND pAssessments.Orphan_Field IN (0,2) AND pPatients.Loc_ID = GROUP BY AssignedEmpID INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , ) Select count(pEmployee.Emp_ID) as ecount, pEmployee.Emp_ID AS AssignedEmpID, FROM agency_1150.pSchedules pSchedules JOIN agency_1150.pPatients pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID AND pPatients.Status IN (0) JOIN hhapowerpath.pEmployee pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID AND pEmployee.Status = 0 WHERE pSchedules.Status = 0 AND pSchedules.Missed > 0 AND pSchedules.Notes IS NULL GROUP BY AssignedEmpID INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , ) SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAssessments.F26, pSchedules.Assmt_ID, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Visit_Type, pSchedules.Skill, pSchedules.Emp_ID, pCareplan.Description, pEmployee.Emp_Last, pEmployee.Emp_First, pSchedules.pgnotesdraft, (SELECT pAss.F25 FROM #Request.prefix_db_agency#.pAssessments pAss WHERE pAss.Patient_ID = pPatients.Patient_ID AND (LEFT(pSchedules.Visit_Type,1) = LEFT(pAss.F26,1) OR (pAssessments.F26 LIKE pSchedules.Visit_Type)) AND pAss.F176 = pSchedules.Visit_Date AND pAss.Status = 0 LIMIT 0,1) AS assessments_completeds, (SELECT pAss.F25 FROM #Request.prefix_db_agency#.pAssessments pAss WHERE pAss.Patient_ID = pAssessments.Patient_ID AND pAss.Admit_ID = pAssessments.Admit_ID AND pAss.F26 = (CASE WHEN (pSchedules.Visit_Type = "1 - SOC - SN") THEN '1 - Start of care - further visits planned' WHEN (pSchedules.Visit_Type = "1 - SOC - PT") THEN '1 - Start of care - further visits planned - Physical Therapy' WHEN (pSchedules.Visit_Type = "1 - SOC - OT") THEN '1 - Start of care - further visits planned - Occupational Therapy' WHEN (pSchedules.Visit_Type = "3 - ROC - SN" || pSchedules.Visit_Type = "3 - ROC - PT") THEN '3 - Resumption of care (after inpatient stay)' WHEN (pSchedules.Visit_Type = "PT Eval") THEN 'PT Adult Evaluation' WHEN (pSchedules.Visit_Type = "OT Eval") THEN 'OT Adult Evaluation' WHEN (pSchedules.Visit_Type = "ST Eval") THEN 'ST Adult Evaluation' WHEN (pSchedules.Visit_Type = "SN Eval") THEN 'SN Adult Evaluation' WHEN (pSchedules.Visit_Type = "MSW Eval") THEN 'MSW Evaluation' WHEN (pSchedules.Visit_Type = "PT FU Eval") THEN 'PT Followup' WHEN (pSchedules.Visit_Type = "OT FU Eval") THEN 'OT Followup' WHEN (pSchedules.Visit_Type = "ST FU Eval") THEN 'ST Followup' WHEN (pSchedules.Visit_Type = "MSW FU Eval") THEN 'MSW Followup' WHEN (pSchedules.Visit_Type = "SN FU Eval") THEN 'SN Followup' WHEN (pSchedules.Visit_Type = "Hospice Comprehensive") THEN 'Hospice Comprehensive' WHEN (pSchedules.Visit_Type = "SN Hospice") THEN 'SN Hospice' WHEN (pSchedules.Visit_Type = "Chaplain Comprehensive Evaluation") THEN 'Chaplain Comprehensive Evaluation' WHEN (pSchedules.Visit_Type = "Hospice Discharge") THEN 'Hospice Discharge' WHEN (pSchedules.Visit_Type = "4 - Recertification") THEN '4 - Recertification (follow-up) reassessment' WHEN (pSchedules.Visit_Type = "4 - Recertification PT") THEN '4 - Recertification (follow-up) reassessment - PT' WHEN (pSchedules.Visit_Type = "4 - Recertification OT") THEN '4 - Recertification (follow-up) reassessment - OT' WHEN (pSchedules.Visit_Type = "5 - Other follow-up") THEN '5 - Other follow-up' WHEN (pSchedules.Visit_Type = "6 - Transfer no discharge") THEN '6 - Transferred to an inpatient facility - patient not discharged from agency' WHEN (pSchedules.Visit_Type = "7 - Transfer discharged") THEN '7 - Transferred to an inpatient facility - patient discharged from agency' WHEN (pSchedules.Visit_Type = "8 - Death") THEN '8 - Death at home' WHEN (pSchedules.Visit_Type = "9 - Discharge from agency") THEN '9 - Discharge from agency' ELSE 0 END) AND pAss.Status = 0 AND pSchedules.Visit_Date = pAss.F176 ORDER BY F25 ASC LIMIT 0,1) AS assessments_completed FROM #Request.prefix_db_agency#.pSchedules LEFT JOIN #Request.prefix_db_agency#.pAssessments ON (pSchedules.Assmt_ID = pAssessments.Assmt_ID OR pSchedules.Assmt_ID = 0) AND pAssessments.Patient_ID = pSchedules.Patient_ID AND pAssessments.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON (pAdmit.Admit_ID = pAssessments.Admit_ID) AND pSchedules.Patient_ID = pAdmit.Patient_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pSched_Procedure ON pSched_Procedure.Schedule_ID = pSchedules.Schedule_ID AND pSched_Procedure.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pCareplan ON pCareplan.Careplan_ID = pSched_Procedure.Careplan_ID AND pCareplan.Status = 0 WHERE pPatients.Status = 0 AND pSchedules.Status = 0 AND pSchedules.Missed = 0 AND pSchedules.Visit_Date < AND pSchedules.pgnotesdraft != 2 AND (LEFT(pSchedules.Visit_Type,1) NOT IN (6) OR pSchedules.Visit_Type IS NULL) GROUP BY pSchedules.Schedule_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC Open patient visit"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT parent_oasis.F26 AS parent_assessment, parent_oasis.F176 AS parent_Assessment_Completed_M0090, pAssessments.Assmt_ID, pAssessments.Patient_ID, pAssessments.F26 AS Assessment_Reason_M0100, pAssessments.F27, pAssessments.F25 AS admission_status, pAssessments.F177 AS Certification_Start, pAssessments.F178 AS Certification_End, pAssessments.F176 AS Assessment_Completed_M0090, pPatients.Pt_First, pPatients.Pt_Last, pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, (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 VitalOrWound = 'vital' AND (Description != 'weight' OR Description != 'Weight') 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#.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 FROM #Request.prefix_db_agency#.pAssessments pAssessments JOIN #Request.prefix_db_agency#.pPatients pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID JOIN #Request.prefix_db_agency#.pAdmit 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 AND parent_oasis.Status = 0 WHERE 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 pAssessments.Admit_ID NOT IN ( SELECT pas.Admit_ID FROM #Request.prefix_db_agency#.pAssessments pas WHERE (pas.F26 LIKE '9%' OR pas.F26 LIKE '7%' OR pas.F26 LIKE '8%') AND pas.Status = 0 AND pas.F25 = 1 ) GROUP BY pAssessments.Assmt_ID Open assessment"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pAssessments.Assmt_ID, pAssessments.F26, pAssessments.F176, pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, pVBP_Inconsist.Message, (SELECT count(Order_ID) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND pCMS4857.Form_Type = 'Form 485' AND pCMS4857.Status = 0) AS count_485 FROM #Request.prefix_db_agency#.pVBP_Inconsist JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pVBP_Inconsist.Assmt_ID JOIN #Request.prefix_db_agency#.pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID 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 WHERE pPatients.Status < 2 AND pAdmit.Status = 0 AND pAssessments.Status = 0 AND pAssessments.F178 >= GROUP BY pVBP_Inconsist.Assmt_ID ORDER BY pVBP_Inconsist.Assmt_ID, pPatients.Pt_Last, pPatients.Patient_ID Open VBP analysis"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAdmit.*, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_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.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.Status = 0 AND Left(pAssessments.F26,1) IN ('4','1') ORDER BY F178 DESC LIMIT 0,1) AS Certification_End, pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last 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 LEFT OUTER JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_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 ) GROUP BY pAdmit.Admit_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC Create assessment"> SELECT COUNT(*) AS recordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) 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 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, 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 LEFT OUTER JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID LEFT OUTER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAdmit.Status = 0 AND pPatients.status = 0 AND pAssessments.status = 0 AND (employee_auth.r_Admit_Pend = 3 OR assessment_assign.Emp_ID IS NULL) 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) 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 SELECT assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.assessment_assign LEFT JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID WHERE assessment_assign.Assmt_ID = LIMIT 0,1 Create assessment"> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Assmt_ID = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Admit_Pend = 3 SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAdmit.*, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_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.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.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 LEFT OUTER JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID LEFT OUTER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pPatients.Loc_ID = AND pPatients.Status = 0 And pAdmit.Status = 0 AND (employee_auth.r_Admit_Pend = 3 OR assessment_assign.Emp_ID IS NULL) 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) GROUP BY pAdmit.Admit_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC View missing OASIS/Evaluation report"> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, (SELECT count(pAssessments.Assmt_ID) FROM #Request.prefix_db_agency#.pAssessments WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.Status = 0 AND pAdmit.Admit_ID = pAssessments.Admit_ID AND pAssessments.F26 = '1 - Start of care - further visits planned' ) AS SOC_count, (SELECT count(pAuths.Auth_ID) FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAdmit.Admit_ID = pAssessments.Admit_ID AND pAssessments.F26 = '1 - Start of care - further visits planned' AND pAuths.Skill = 'SN' AND pAuths.Status = 0 ) AS SOCauth_count, (SELECT count(pAssessments.Assmt_ID) FROM #Request.prefix_db_agency#.pAssessments WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.Status = 0 AND pAssessments.F26 = '1 - Start of care - further visits planned - Physical Therapy' ) AS SOCPT_count, (SELECT count(pAuths.Auth_ID) FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = '1 - Start of care - further visits planned - Physical Therapy' AND pAuths.Skill = 'PT' AND pAuths.Status = 0 ) AS SOCPTauth_count, (SELECT count(pAssessments.Assmt_ID) FROM #Request.prefix_db_agency#.pAssessments WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.Status = 0 AND LEFT(pAssessments.F26,1) = '4' ) AS Recert_count, (SELECT count(pAuths.Auth_ID) FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '4' AND pAuths.Status = 0 ) AS Recertauth_count, (SELECT count(pAssessments.Assmt_ID) FROM #Request.prefix_db_agency#.pAssessments WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.Status = 0 AND pAssessments.F26 = 'PT Adult Evaluation' ) AS PTAdult_count, (SELECT count(pAuths.Auth_ID) FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAuths.Skill = 'PT' AND pAuths.Status = 0 ) AS PTAdultauth_count, (SELECT count(pAssessments.Assmt_ID) FROM #Request.prefix_db_agency#.pAssessments WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.Status = 0 AND pAssessments.F26 = 'OT Adult Evaluation' ) AS OTAdult_count, (SELECT count(pAuths.Auth_ID) FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAuths.Skill = 'OT' AND pAuths.Status = 0 ) AS OTAdultauth_count, (SELECT count(pAssessments.Assmt_ID) FROM #Request.prefix_db_agency#.pAssessments WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.Status = 0 AND pAssessments.F26 = 'SN Adult Evaluation' ) AS SNAdult_count, (SELECT count(pAuths.Auth_ID) FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAuths.Skill = 'SN' AND pAuths.Status = 0 ) AS SNAdultauth_count, (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 FROM #Request.prefix_db_agency#.pPatients JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND pAdmit.Status = 0 AND pAdmit.drafts = 0 WHERE pPatients.Status = 0 AND pPatients.Loc_ID = GROUP BY pPatients.Patient_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last 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 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 AND pEmployee.Status = 0 WHERE pAssessments.Patient_ID = AND pAssessments.Status = 0 AND pEmployee.Loc_ID = View missing authorization report for " & HTMLEditFormat(getMissingAuthPatients.PatientLast & ", " & getMissingAuthPatients.PatientFirst) & ""> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedule_require INNER JOIN #Request.prefix_db_agency#.assessment_assign ON pSchedule_require.Assmt_ID = assessment_assign.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE ((pSchedule_require.SOC_SN = 0 AND pSchedule_require.authSkill = 'SN' AND pSchedule_require.service = '1 - SOC - SN') OR (pSchedule_require.SOC_PT = 0 AND pSchedule_require.authSkill = 'PT' AND pSchedule_require.service = '1 - SOC - PT') OR (pSchedule_require.SOC_OT = 0 AND pSchedule_require.authSkill = 'OT' AND pSchedule_require.service = '1 - SOC - OT') OR (pSchedule_require.ROC_SN = 0 AND pSchedule_require.authSkill = 'SN' AND pSchedule_require.service = '3 - ROC - SN') OR (pSchedule_require.ROC_PT = 0 AND pSchedule_require.authSkill = 'PT' AND pSchedule_require.service = '3 - ROC - PT') OR (pSchedule_require.SN_Eval = 0 AND pSchedule_require.authSkill = 'SN' AND pSchedule_require.service = 'SN Eval') OR (pSchedule_require.PT_Eval = 0 AND pSchedule_require.authSkill = 'PT' AND pSchedule_require.service = 'PT Eval') OR (pSchedule_require.OT_Eval = 0 AND pSchedule_require.authSkill = 'OT' AND pSchedule_require.service = 'OT Eval') OR (pSchedule_require.ST_Eval = 0 AND pSchedule_require.authSkill = 'ST' AND pSchedule_require.service = 'ST Eval') OR (pSchedule_require.MSW_Eval = 0 AND pSchedule_require.authSkill = 'MSW' AND pSchedule_require.service = 'MSW Eval') OR (pSchedule_require.authassmt_id = 0 AND pSchedule_require.schedule_count = 0)) AND pSchedule_require.service != '0' AND pSchedule_require.query_type = 'Insert auths WITH OASIS/Eval' AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Sched_Assmt = 3 GROUP BY pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last SELECT DISTINCT pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedule_require INNER JOIN #Request.prefix_db_agency#.assessment_assign ON pSchedule_require.Assmt_ID = assessment_assign.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE ((pSchedule_require.Recert_visits = 0 OR pSchedule_require.PTfollow_visits = 0 OR pSchedule_require.OTfollow_visits = 0 OR pSchedule_require.Resump_visits = 0) AND pSchedule_require.query_type = 'Insert auths WITH OASIS/Eval') AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Sched_Assmt = 3 GROUP BY pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last UNION SELECT DISTINCT pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedule_require INNER JOIN #Request.prefix_db_agency#.assessment_assign ON pSchedule_require.Assmt_ID = assessment_assign.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedule_require.transfer_visits = 0 AND pSchedule_require.death_visits = 0 AND pSchedule_require.discharge_visits = 0 AND pSchedule_require.Resump_visits = 0 AND pSchedule_require.query_type = 'Missed ResumptionCare' AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Sched_Assmt = 3 GROUP BY pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last SELECT DISTINCT pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedule_require INNER JOIN #Request.prefix_db_agency#.assessment_assign ON pSchedule_require.Assmt_ID = assessment_assign.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedule_require.dischargeoasis_count = 0 AND ((pSchedule_require.total_recertifications > 0 AND pSchedule_require.recertification_count = 0) OR (pSchedule_require.PT_need = 1 AND pSchedule_require.PT_summary = 0 AND pSchedule_require.pt_noauth = 0) OR (pSchedule_require.OT_need = 1 AND pSchedule_require.OT_summary = 0 AND pSchedule_require.ot_noauth = 0) OR (pSchedule_require.ST_need = 1 AND pSchedule_require.ST_summary = 0 AND pSchedule_require.st_noauth = 0) OR (pSchedule_require.MSW_need = 1 AND pSchedule_require.MSW_summary = 0 AND pSchedule_require.msw_noauth = 0) OR pSchedule_require.schedule_count = 0) AND pSchedule_require.query_type = 'Insert auths WITHOUT OASIS/Eval' AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Sched_Assmt = 3 GROUP BY pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last SELECT DISTINCT pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedule_require INNER JOIN #Request.prefix_db_agency#.assessment_assign ON pSchedule_require.Assmt_ID = assessment_assign.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedule_require.transfer_visits = 0 AND pSchedule_require.death_visits = 0 AND pSchedule_require.discharge_visits = 0 AND pSchedule_require.F26 = '3 - Resumption of care (after inpatient stay)' AND pSchedule_require.schedule_count = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Sched_Assmt = 3 GROUP BY pSchedule_require.Patient_ID, pSchedule_require.PatientLast, pSchedule_require.PatientFirst, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last SELECT DISTINCT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAdmit.Case_Manager AS Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pPatients JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID LEFT JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Patient_ID = pPatients.Patient_ID AND pSchedules.Status = 0 AND pSchedules.Missed = '0' INNER JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_Manager = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pPatients.Status = 0 AND pAdmit.Status = 0 AND pAdmit.drafts = 0 AND pSchedules.Schedule_ID IS NULL AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.patients_ai WHERE patients_ai.Patient_ID = pPatients.Patient_ID AND patients_ai.status = 0 ) AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Sched_Assmt = 3 GROUP BY pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pAdmit.Case_Manager, pEmployee.Emp_First, pEmployee.Emp_Last View scheduling needed report for " & HTMLEditFormat(getSchedulingNeededPatients.PatientLast & ", " & getSchedulingNeededPatients.PatientFirst) & ""> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_Auth FROM #Request.prefix_db_agency#.pAdmit pAdmit INNER JOIN #Request.prefix_db_lookup#.pEmployee pEmployee ON pAdmit.Case_manager = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAdmit.Status = 0 AND pAdmit.Case_manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Auth = 1 SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Emp_ID = AND Header = AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_Auth, pAuths.AutoAuth_ID, pAuths.Patient_ID, pAuths.Date_End, pAuths.Date_Start, pAuths.Skill, pPatients.Pt_First, pPatients.Pt_Last, pPayer.Pay_Name, pRevenue_Code.Visit_Type, pRevenue_Code.Rev_Code FROM #Request.prefix_db_agency#.pAdmit pAdmit INNER JOIN #Request.prefix_db_lookup#.pEmployee pEmployee ON pAdmit.Case_manager = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID INNER JOIN #Request.prefix_db_agency#.pAuths ON pAdmit.Patient_ID = pAuths.Patient_ID INNER JOIN #Request.prefix_db_agency#.pPatients ON pAuths.Patient_ID = pPatients.Patient_ID INNER JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pAuths.Rev_ID INNER JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = pAuths.PtPayer_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID WHERE pAdmit.Status = 0 AND pAdmit.Case_manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Auth = 3 AND pAuths.STATUS = 0 AND pRevenue_Code.Status = 0 AND pPtPayer.Status = 0 AND pPayer.Status = 0 AND pPatients.Status = 0 AND pAuths.Date_End >= NOW() GROUP BY pAuths.AutoAuth_ID ORDER BY pAuths.Date_End ASC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) // Get today's date today = now(); // Find the day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday) dayOfWeek = dayOfWeek(today); // Calculate the difference in days from Sunday daysSinceSunday = dayOfWeek - 1; // Calculate the start of the week (Sunday) startOfWeek = dateAdd("d", -daysSinceSunday, today); SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_Visit_Freq, pAuths_weeks.Auths_weeks_ID, pAuths_weeks.Auth_ID, pAuths_weeks.Week_No, pAuths_weeks.Week_Start, pAuths_weeks.Week_End, pAuths_weeks.Total_visits, pPatients.Patient_ID, pPatients.Pt_First, pPatients.Pt_Last, pAuths.Skill, pAssessments.F26, pAssessments.Assmt_ID, (SELECT count(DISTINCT Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules WHERE pSchedules.Status = 0 AND pSchedules.Auth_ID = pAuths.Auth_ID AND pAuths.Skill = pSchedules.Skill AND Visit_Date <= pAuths_weeks.Week_End AND Visit_Date >= pAuths_weeks.Week_Start AND Missed = 0) AS Visit_Count FROM #Request.prefix_db_agency#.pAuths_weeks INNER JOIN #Request.prefix_db_agency#.pAuths ON pAuths_weeks.Auth_ID = pAuths.Auth_ID AND pAuths.Status = 0 INNER JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 INNER JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID INNER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_manager = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID INNER JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = pAuths.PtPayer_ID AND pPtPayer.status = 0 INNER JOIN #Request.prefix_db_agency#.pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID AND pPayer.status = 0 AND LEFT(pPayer.M0150,1) IN ('1','2') WHERE pPatients.Status < 1 AND pAdmit.Status = 0 AND pAdmit.drafts = 0 AND pAdmit.Case_manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Visit_Freq = 1 AND pAuths.Visitfreq_Placeholder = 0 AND pAuths_weeks.Status = 0 AND (pAuths_weeks.Total_visits > 0 OR pAuths.prn_visits > 0) AND pAuths_weeks.Week_Start <= 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.F176 <= AND pAssessments.Status = 0 ) GROUP BY pAuths_weeks.Auths_weeks_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pAuths_weeks.*, pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAuths.Skill, pAssessments.F26, pAssessments.Assmt_ID, pAuths_weeks.Total_visits as totalvisit, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, (SELECT count(DISTINCT Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules WHERE pSchedules.Status = 0 AND pSchedules.Auth_ID = pAuths.Auth_ID AND pAuths.Skill = pSchedules.Skill AND Visit_Date <= pAuths_weeks.Week_End AND Visit_Date >= pAuths_weeks.Week_Start AND Missed = 0) AS Visit_Count FROM #Request.prefix_db_agency#.pAuths_weeks JOIN #Request.prefix_db_agency#.pAuths ON pAuths_weeks.Auth_ID = pAuths.Auth_ID AND pAuths.Status = 0 JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 JOIN #Request.prefix_db_agency#.pPtPayer pPtPayer ON pPtPayer.PtPayer_ID = pAuths.PtPayer_ID AND pPtPayer.status = 0 JOIN #Request.prefix_db_agency#.pPayer pp ON pPtPayer.Payer_ID = pp.Pay_ID AND pp.status = 0 AND LEFT(pp.M0150,1) IN ('1','2') JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID INNER JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pPatients.Status < 1 AND Week_Start <= AND Week_Start >= '2025-01-01' AND pAuths.Visitfreq_Placeholder = 0 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.F176 <= AND pAssessments.Status = 0 ) AND pAssessments.Admit_ID IN ( SELECT pAdmit.Admit_ID FROM #Request.prefix_db_agency#.pAdmit WHERE pAdmit.Status = 0 AND pAdmit.drafts = 0 ) AND pAuths_weeks.Status = 0 AND (pAuths_weeks.Total_visits > 0 OR pAuths.prn_visits > 0) AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Visit_Freq = 3 GROUP BY pAuths_weeks.Auths_weeks_ID, pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pAuths.Skill, pAssessments.F26, pAssessments.Assmt_ID, pAuths_weeks.Total_visits, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAuths.Skill, pAuths.Auth_ID, pAuths.Total_Visits as totalvisit, pAssessments.F26, pAssessments.Assmt_ID, pAssessments.F177, pAssessments.F178, assessment_assign.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, (SELECT count(DISTINCT Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules WHERE pSchedules.Status = 0 AND pSchedules.Auth_ID = pAuths.Auth_ID AND pAuths.Skill = pSchedules.Skill AND Missed = 0) AS Visit_Count FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0 JOIN #Request.prefix_db_agency#.pPtPayer pPtPayer ON pPtPayer.PtPayer_ID = pAuths.PtPayer_ID AND pPtPayer.status = 0 JOIN #Request.prefix_db_agency#.pPayer pp ON pPtPayer.Payer_ID = pp.Pay_ID AND pp.status = 0 AND LEFT(pp.M0150,1) IN ('1','2') JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID INNER JOIN #Request.prefix_db_agency#.assessment_assign ON assessment_assign.Assmt_ID = pAssessments.Assmt_ID INNER JOIN #Request.prefix_db_lookup#.pEmployee ON assessment_assign.Emp_ID = pEmployee.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pPatients.Status < 1 AND pAuths.Status = 0 AND pAuths.Visitfreq_Placeholder = 0 AND pAuths.date_start IS NULL AND pAuths.date_end IS NULL AND pAuths.Total_Visits = 0 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.F176 <= AND pAssessments.Status = 0 ) AND pAssessments.Admit_ID IN ( SELECT pAdmit.Admit_ID FROM #Request.prefix_db_agency#.pAdmit WHERE pAdmit.Status = 0 AND pAdmit.drafts = 0 ) AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_Visit_Freq = 3 ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC View Report"> View Report"> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header LIKE AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_485_Create, pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pPatients.Status as pStatus, pAssessments.Assmt_ID, pAssessments.F26, pAssessments.F177, pAssessments.F178, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND Status = 0 AND Form_Type = 'Form 485') AS new485_require FROM #Request.prefix_db_agency#.pAssessments INNER JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status IN (0,1,4) INNER JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 INNER JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 INNER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAssessments.status = 0 AND pAssessments.F25 = 1 AND Left(pAssessments.F26,1) IN ('1','4') AND pSchedules.Visit_Date > pAssessments.F177 AND pAssessments.Orphan_Field IN (1) AND pPatients.Med_Recon_Field IN (1) AND pAdmit.Case_Manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_485_Create = 3 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 F176 < ) GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_485_Create, pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pPatients.Status as pStatus, pAssessments.Assmt_ID, pAssessments.F26, pAssessments.F177, pAssessments.F178, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID IN ( SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.Admit_ID = pAdmit.Admit_ID AND pass.status = 0 ) AND Status = 0 AND Form_Type = 'Form 485') AS new485_require FROM #Request.prefix_db_agency#.pAssessments INNER JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status IN (0,1,4) INNER JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 INNER JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 INNER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAssessments.status = 0 AND pAssessments.F25 = 1 AND (F26 LIKE 'PT Adult%' OR F26 LIKE 'OT Adult%' OR F26 LIKE 'ST Adult%' OR F26 LIKE 'SN Adult%') AND pSchedules.Visit_Date > pAssessments.F177 AND pAdmit.Admit_ID NOT IN ( SELECT Admit_ID FROM #Request.prefix_db_agency#.pAssessments WHERE Status = 0 AND Left(F26,1) IN ('1','4') ) AND pAssessments.Orphan_Field NOT IN (1) AND pPatients.Med_Recon_Field NOT IN (1) AND pAdmit.Case_Manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_485_Create = 3 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 F176 < ) GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_485_Create, pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pPatients.Status as pStatus, pAssessments.Assmt_ID, pAssessments.F26, pAssessments.F177, pAssessments.F178, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND Status = 0 AND Form_Type = 'Form 485') AS new485_require FROM #Request.prefix_db_agency#.pAssessments INNER JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status IN (0,1,4) INNER JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 INNER JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 INNER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAssessments.status = 0 AND pAssessments.F25 = 1 AND Left(pAssessments.F26,1) IN ('1','4') AND pSchedules.Visit_Date > pAssessments.F177 AND pAssessments.Orphan_Field IN (1) AND pPatients.Med_Recon_Field IN (1) AND pAdmit.Case_Manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_485_Create = 1 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 F176 < ) GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, employee_auth.eaRole, employee_auth.r_485_Create, pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pPatients.Status as pStatus, pAssessments.Assmt_ID, pAssessments.F26, pAssessments.F177, pAssessments.F178, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID IN ( SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.Admit_ID = pAdmit.Admit_ID AND pass.status = 0 ) AND Status = 0 AND Form_Type = 'Form 485') AS new485_require FROM #Request.prefix_db_agency#.pAssessments INNER JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status IN (0,1,4) INNER JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 INNER JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 INNER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pAssessments.status = 0 AND pAssessments.F25 = 1 AND (F26 LIKE 'PT Adult%' OR F26 LIKE 'OT Adult%' OR F26 LIKE 'ST Adult%' OR F26 LIKE 'SN Adult%') AND pSchedules.Visit_Date > pAssessments.F177 AND pAdmit.Admit_ID NOT IN ( SELECT Admit_ID FROM #Request.prefix_db_agency#.pAssessments WHERE Status = 0 AND Left(F26,1) IN ('1','4') ) AND pAssessments.Orphan_Field NOT IN (1) AND pPatients.Med_Recon_Field NOT IN (1) AND pAdmit.Case_Manager > 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.r_485_Create = 1 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 F176 < ) GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND Description LIKE AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pAssessments.Admit_ID FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID WHERE (pAssessments.F26 LIKE '9%' OR pAssessments.F26 LIKE '7%' OR pAssessments.F26 LIKE '8%') AND pAssessments.Status = 0 AND pAssessments.F25 = 1 AND pAdmit.drafts = 0 AND pAdmit.Status = 0 GROUP BY pAdmit.Admit_ID SELECT DISTINCT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pAdmit.Case_Manager AS Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pPatients pPatients LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND (pAdmit.Status = 0 OR pAdmit.Status = 4) AND pAdmit.drafts = 1 AND pAdmit.Admit_ID NOT IN (#admit_ids#) LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_Manager = pEmployee.Emp_ID WHERE pPatients.status = 0 AND pPatients.Loc_ID = AND ( (pAdmit.Admit_ID IS NOT NULL) OR NOT EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAdmit pa WHERE pa.Patient_ID = pPatients.Patient_ID AND (pa.Status = 0 OR pa.Status = 4) ) ) AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pProblem pProblem JOIN #Request.prefix_db_agency#.pAssessments pAssessments ON pAssessments.Assmt_ID = pProblem.Assmt_ID WHERE pProblem.Status = 0 AND pProblem.M0_No LIKE 'M0%' AND pAssessments.Status = 0 AND ( (pAdmit.Admit_ID IS NOT NULL AND pAssessments.Admit_ID = pAdmit.Admit_ID) OR (pAdmit.Admit_ID IS NULL AND pAssessments.Patient_ID = pPatients.Patient_ID) ) ) GROUP BY pPatients.Patient_ID SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View incomplete admissions"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pAdmit.Case_Manager AS Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pPatients pPatients LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND (pAdmit.Status = 0 OR pAdmit.Status = 4) AND pAdmit.drafts = 1 AND pAdmit.Admit_ID NOT IN (#admit_ids#) LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_Manager = pEmployee.Emp_ID WHERE pPatients.status = 0 AND pPatients.Loc_ID = AND ( (pAdmit.Admit_ID IS NOT NULL) OR NOT EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAdmit pa WHERE pa.Patient_ID = pPatients.Patient_ID AND (pa.Status = 0 OR pa.Status = 4) ) ) AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pProblem pProblem WHERE pProblem.Status = 0 AND pProblem.M0_No LIKE 'M1%' AND (pProblem.Path_ID IS NULL OR pProblem.Path_ID = 0) AND ( (pAdmit.Admit_ID IS NOT NULL AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAssessments pAssessments WHERE pAssessments.Admit_ID = pAdmit.Admit_ID AND pAssessments.Assmt_ID = pProblem.Assmt_ID AND pAssessments.Status = 0 )) OR (pAdmit.Admit_ID IS NULL AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAssessments pAssessments WHERE pAssessments.Patient_ID = pPatients.Patient_ID AND pAssessments.Assmt_ID = pProblem.Assmt_ID AND pAssessments.Status = 0 )) ) ) GROUP BY pPatients.Patient_ID SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View incomplete admissions"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pAssessments.Assmt_ID, pAssessments.Record_creation_Date, pAssessments.Assmt_ID, pAssessments.F27, 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, pAssessments.F176, pPatients.Pt_First, pPatients.Pt_Last, pPatients.Pt_Middle, pCareplan.Careplan_ID, pCareplan.Specifics, pCareplan.Description, pCareplan.Notes, pCareplan.Skill, pCareplan.QA_approve as Goals_QA, pCareplan.Date_Create, pCareplan.Order_Date, pCareplan.QA_approve, pCareplan.Item_ID, pCareplan.Notes, pProblem.Description AS Problem_Description, pProblem.Answer, pProblem.Skill, pProblem.Assmt_ID, pProblem.Path_ID, pEmployee.Emp_First, pEmployee.Emp_Last, pPayer.Pay_Name FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID AND pPatients.Status IN (0,1,4) JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_agency#.pCareplan ON pCareplan.Assmt_ID = pAssessments.Assmt_ID AND pCareplan.Status = 1 LEFT OUTER JOIN #Request.prefix_db_lookup#.pPathway ON pPathway.Type = 'goal' AND pPathway.Item_ID = pCareplan.Item_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pProblem ON pProblem.Assmt_ID = pAssessments.Assmt_ID AND pProblem.Status = 0 AND pPathway.Pathway_ID = pProblem.Path_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pCareplan.Created_By LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.Patient_ID = pPatients.Patient_ID AND pPtPayer.Status = 0 AND pPtPayer.Payer_Responsibility = 'P - Primary' LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0 WHERE pPatients.Loc_ID = AND pAssessments.Status = 0 AND pAssessments.F25 = 1 AND pAssessments.F176 >= '2025-08-20' AND pCareplan.Type = 'goal' AND pCareplan.QA_approve IN (0) GROUP BY pCareplan.Careplan_ID ORDER BY pPatients.Pt_First ASC SELECT DISTINCT pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedules.Patient_ID = AND pSchedules.Status = 0 AND pAssessments.Status = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.eaRole LIKE '%Field%' AND employee_auth.eaRole NOT LIKE '%Project Manager%' AND employee_auth.eaRole NOT LIKE '%Quality%' View disabled goals"> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Assmt_ID = AND Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Project Manager%' OR employee_auth.eaRole LIKE '%Clinical Quality Assurance%') View disabled goals"> SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Assmt_ID = AND Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT pAssessments.Assmt_ID, pAssessments.Record_creation_Date, pAssessments.Assmt_ID, pAssessments.F27, pAssessments.QA_Count, 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, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.Admit_ID = pAssessments.Admit_ID AND pass.status = 0 AND pass.F25 = 1 AND LEFT(pass.F26,1) IN (1,9) AND pass.QA_Count != 1) AS completed_oasis, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.Admit_ID = pAssessments.Admit_ID AND pass.status = 0 AND pass.F25 = 1 AND LEFT(pass.F26,1) IN (9) AND pass.QA_Count != 1 AND pass.F197 IS NULL) AS dicharge_transmitted, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F147 ELSE pAssessments.F118 END AS M1400, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F74 ELSE pAssessments.F77 END AS M1800, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F75 ELSE pAssessments.F78 END AS M1810, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F76 ELSE pAssessments.F79 END AS M1820, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F77 ELSE pAssessments.F80 END AS M1830, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F78 ELSE pAssessments.F81 END AS M1840, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F79 ELSE pAssessments.F82 END AS M1845, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F80 ELSE pAssessments.F83 END AS M1850, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F81 ELSE pAssessments.F84 END AS M1860, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F156 ELSE pAssessments.F163 END AS M1870, CASE WHEN LEFT(pAssessments.F26,1) = 1 THEN pAssessments.F204 ELSE pAssessments.F135 END AS M2200, pPatients.Pt_First, pPatients.Pt_Last, pPatients.Pt_Middle, pAssessments.*, pEmployee.Emp_ID, 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 AND pPatients.Status IN (0,1) JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_Manager = pEmployee.Emp_ID WHERE pPatients.Loc_ID = AND pAssessments.Status = 0 AND pAssessments.F25 = 1 AND LEFT(pAssessments.F26,1) IN (1,9) AND pAssessments.QA_Count != 1 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_First, pPatients.Patient_ID, Assessment_Reason_M0100 ASC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Assmt_ID = AND Header = AND Daily_QA_Date = AND status = 0 View discharge approval"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT Eligibility.Elig_ID, pPayer.Pay_Name, Eligibility.Start_Date, pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pAdmit.Case_Manager AS Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last, (SELECT COUNT(*) FROM #Request.prefix_db_agency#.Eligibility WHERE PtPayer_ID = pPtPayer.PtPayer_ID AND status = 0 AND (Expiration_Date >= NOW() OR (Expiration_Date IS NULL AND Start_Date IS NOT NULL))) AS expire_Count FROM #Request.prefix_db_agency#.pPtPayer pPtPayer JOIN #Request.prefix_db_agency#.pPayer pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID AND pPayer.Status = 0 AND pPayer.Eligibility = 1 JOIN #Request.prefix_db_agency#.pPatients pPatients ON pPtPayer.Patient_ID = pPatients.Patient_ID AND pPatients.Status < 2 LEFT OUTER JOIN #Request.prefix_db_agency#.Eligibility ON Eligibility.PtPayer_ID = pPtPayer.PtPayer_ID AND Eligibility.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND pAdmit.Status = 0 LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_Manager = pEmployee.Emp_ID WHERE pPtPayer.status = 0 AND (Eligibility.Elig_ID IS NULL OR Eligibility.Start_Date IS NULL) AND (Eligibility.Notes != 'Automated eligibility' OR Eligibility.Notes IS NULL) AND pPatients.Loc_ID = ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC, Eligibility.Elig_ID DESC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View missing eligibility"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pAdmit.Case_Manager AS Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pPatients pPatients LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND (pAdmit.Status = 0 OR pAdmit.Status = 4) AND pAdmit.drafts = 1 AND pAdmit.Admit_ID NOT IN (#admit_ids#) LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pAdmit.Case_Manager = pEmployee.Emp_ID WHERE pPatients.status = 0 AND pPatients.Loc_ID = AND ( (pAdmit.Admit_ID IS NOT NULL) OR NOT EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.pAdmit pa WHERE pa.Patient_ID = pPatients.Patient_ID AND (pa.Status = 0 OR pa.Status = 4) ) ) GROUP BY pPatients.Patient_ID ORDER BY pPatients.Patient_ID ASC, pAdmit.M0030_START_CARE_DT DESC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View incomplete admissions"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT * FROM #Request.prefix_db_agency#.p487require WHERE new485_require > 0 AND (problemcms_Id > 0 OR ptmedscms_Id > 0 OR careplancms_Id > 0 OR authcms_id > 0) GROUP BY Patient_ID SELECT DISTINCT pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedules.Patient_ID = AND pSchedules.Status = 0 AND pAssessments.Status = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.eaRole LIKE '%Field%' AND employee_auth.eaRole NOT LIKE '%Project Manager%' AND employee_auth.eaRole NOT LIKE '%Quality%' SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View 487 require report"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Project Manager%' OR employee_auth.eaRole LIKE '%Clinical Quality Assurance%') SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View 487 require report"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pPatients.Patient_ID, pPatients.Status, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pCMS4857.Order_ID, pCMS4857.Form_Type, pCMS4857.Date_Sent, pCMS4857.Certification_From, pCMS4857.Certification_To, pCMS4857.MD_Digital_Signature, pCMS4857.Date_Create, DATEDIFF(NOW(), pCMS4857.Date_Create) AS age, pCMS4857.Order_description, pCMS4857.Form_Status, pPhysicians.Phys_ID, pEmployee.Emp_Last, pEmployee.Emp_First, pEmployee.Emp_ID, pPhysicians.Phys_Last, pPhysicians.Phys_First, pAssessments.Status FROM #Request.prefix_db_agency#.pCMS4857 LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pCMS4857.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pPhysicians ON pCMS4857.Render_Phys_ID = pPhysicians.Phys_ID JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Patient_ID = pPatients.Patient_ID AND pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.Status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID WHERE pPatients.Loc_ID = AND pCMS4857.Status = 0 AND (pCMS4857.Form_Status LIKE '%send%') AND pPatients.Status < 2 AND pAssessments.Status = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 GROUP BY pCMS4857.Order_ID, pSchedules.Emp_ID ORDER BY pPatients.Pt_Last, pCMS4857.Certification_To SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View unsent orders"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pPatients.Patient_ID, pPatients.Status, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pCMS4857.Order_ID, pCMS4857.Form_Type, pCMS4857.Date_Sent, pCMS4857.Certification_From, pCMS4857.Certification_To, pCMS4857.MD_Digital_Signature, pCMS4857.Date_Create, DATEDIFF(NOW(), pCMS4857.Date_Sent) AS age, pCMS4857.Order_description, pCMS4857.Form_Status, pPhysicians.Phys_ID, pPhysicianEmp.Emp_Last, pPhysicianEmp.Emp_First, pScheduleEmp.Emp_ID, pScheduleEmp.Emp_First AS Emp_First, pScheduleEmp.Emp_Last AS Emp_Last, pAssessments.Status FROM #Request.prefix_db_agency#.pCMS4857 LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pCMS4857.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pPhysicians ON pCMS4857.Render_Phys_ID = pPhysicians.Phys_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee pPhysicianEmp ON pPhysicians.Emp_ID = pPhysicianEmp.Emp_ID JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Patient_ID = pPatients.Patient_ID AND pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.Status = 0 JOIN #Request.prefix_db_lookup#.pEmployee pScheduleEmp ON pScheduleEmp.Emp_ID = pSchedules.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pScheduleEmp.Emp_ID = employee_auth.eaID WHERE pPatients.Loc_ID = AND (pCMS4857.Form_Status <> 'Signed and Received') AND pCMS4857.Form_Status <> 'Printed Only' AND pCMS4857.Form_Status <> 'E-Signed and Received' AND pCMS4857.Form_Status <> 'Signed & Received via Fax' AND pCMS4857.Status = 0 AND pPatients.Status < 2 AND pAssessments.Status = 0 AND pScheduleEmp.Loc_ID = AND pScheduleEmp.Status = 0 AND employee_auth.eaRole LIKE '%Field%' AND employee_auth.eaRole NOT LIKE '%Project Manager%' AND employee_auth.eaRole NOT LIKE '%Quality%' GROUP BY pCMS4857.Order_ID, pSchedules.Emp_ID ORDER BY pPatients.Pt_Last, pCMS4857.Certification_To SELECT pPatients.Patient_ID, pPatients.Status, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pCMS4857.Order_ID, pCMS4857.Form_Type, pCMS4857.Date_Sent, pCMS4857.Certification_From, pCMS4857.Certification_To, pCMS4857.MD_Digital_Signature, pCMS4857.Date_Create, DATEDIFF(NOW(), pCMS4857.Date_Sent) AS age, pCMS4857.Order_description, pCMS4857.Form_Status, pPhysicians.Phys_ID, pPhysicianEmp.Emp_Last, pPhysicianEmp.Emp_First, pQAEmployee.Emp_ID, pQAEmployee.Emp_First AS Emp_First, pQAEmployee.Emp_Last AS Emp_Last, pAssessments.Status FROM #Request.prefix_db_agency#.pCMS4857 LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pCMS4857.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPatients ON pAssessments.Patient_ID = pPatients.Patient_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pPhysicians ON pCMS4857.Render_Phys_ID = pPhysicians.Phys_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee pPhysicianEmp ON pPhysicians.Emp_ID = pPhysicianEmp.Emp_ID CROSS JOIN ( SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Project Manager%' OR employee_auth.eaRole LIKE '%Clinical Quality Assurance%') ) AS pQAEmployee WHERE pPatients.Loc_ID = AND (pCMS4857.Form_Status <> 'Signed and Received') AND pCMS4857.Form_Status <> 'Printed Only' AND pCMS4857.Form_Status <> 'E-Signed and Received' AND pCMS4857.Form_Status <> 'Signed & Received via Fax' AND pCMS4857.Status = 0 AND pPatients.Status < 2 AND pAssessments.Status = 0 GROUP BY pCMS4857.Order_ID, pQAEmployee.Emp_ID ORDER BY pPatients.Pt_Last, pCMS4857.Certification_To SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View unsigned orders"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View unsigned orders"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DATE_ADD(Eligibility.Expiration_Date, INTERVAL 10 DAY), DATEDIFF(Eligibility.Expiration_Date, now()) AS diff_days, pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, Eligibility.*, pPayer.Pay_Name FROM #Request.prefix_db_agency#.Eligibility JOIN #Request.prefix_db_agency#.pPtPayer ON Eligibility.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status = 0 JOIN #Request.prefix_db_agency#.pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID AND pPayer.Status = 0 AND pPayer.Eligibility = 1 JOIN #Request.prefix_db_agency#.pPatients ON pPtPayer.Patient_ID = pPatients.Patient_ID AND pPatients.Status < 2 WHERE Eligibility.Status = 0 AND Eligibility.Expiration_Date BETWEEN Now() AND DATE_ADD(NOW(), INTERVAL 60 DAY) ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Scheduling%' OR employee_auth.eaRole LIKE '%Project Manager%') SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring eligibility"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pAuths.*, (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 CONCAT("Discharge Summary - ",pAuths.Skill) OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND Communication.Status = 0 ) AS communication_discharge, pRevenue_Code.Visit_Type,pRevenue_Code.Rev_Code,pRevenue_Code.HCPCS,pPtPayer.Sub_First, pPayer.Pay_Name,pEmployee.Emp_Last, pEmployee.Emp_First, CONCAT(pEmployee.Emp_First , ", ", pEmployee.Emp_Last) AS name_by, pPatients.Pt_First,pPatients.Pt_Last FROM #Request.prefix_db_agency#.pAuths JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pAuths.Rev_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = pAuths.PtPayer_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAuths.Patient_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pAuths.Created_by = pEmployee.Emp_ID WHERE pAuths.STATUS = 0 AND pRevenue_Code.Status = 0 AND pPtPayer.Status = 0 AND pPayer.Status = 0 AND pPatients.Status = 0 AND pAuths.Date_End BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 20 DAY) GROUP BY pAuths.AutoAuth_ID ORDER BY pAuths.Date_End ASC SELECT DISTINCT pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedules.Patient_ID = AND pSchedules.Status = 0 AND pAssessments.Status = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.eaRole LIKE '%Field%' AND employee_auth.eaRole NOT LIKE '%Project Manager%' AND employee_auth.eaRole NOT LIKE '%Quality%' SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring authorizations"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Project Manager%' OR employee_auth.eaRole LIKE '%Clinical Quality Assurance%') SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring authorizations"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT pAssessments.Assmt_ID, pAssessments.Record_creation_Date, pAssessments.Assmt_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, pPatients.Pt_Middle 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 WHERE pPatients.Loc_ID = AND pPatients.Status = 0 AND pAssessments.Status = 0 AND (pAssessments.F26 LIKE '1%' OR pAssessments.F26 LIKE '4%' OR pAssessments.F26 LIKE '3%') AND pAssessments.F178 >= now() 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) UNION SELECT pAssessments.Assmt_ID, pAssessments.Record_creation_Date, pAssessments.Assmt_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, pPatients.Pt_Middle 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 WHERE pPatients.Loc_ID = AND pPatients.Status = 0 AND pAssessments.Status = 0 AND (pAssessments.F25 = 0 OR pAssessments.F25 IS NULL) AND (pAssessments.F26 LIKE '%Eval%' OR pAssessments.F26 LIKE '%Followup%') AND pAssessments.F176 <= now() 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) GROUP BY pAssessments.Assmt_ID SELECT DISTINCT pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedules.Patient_ID = AND pSchedules.Status = 0 AND pAssessments.Status = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.eaRole LIKE '%Field%' AND employee_auth.eaRole NOT LIKE '%Project Manager%' AND employee_auth.eaRole NOT LIKE '%Quality%' AND EXISTS ( SELECT 1 FROM #Request.prefix_db_agency#.assessment_assign WHERE assessment_assign.Assmt_ID = AND assessment_assign.Emp_ID = pSchedules.Emp_ID ) SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Assmt_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring assessments"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Project Manager%' OR employee_auth.eaRole LIKE '%Clinical Quality Assurance%') SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Assmt_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring assessments"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , ) SELECT pSchedules.*,pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pPatients.Pt_Agy_ID, pAssessments.F26, pAssessments.Assmt_ID,pAuths.Total_Visits, (SELECT ps.Visit_Date FROM #Request.prefix_db_agency#.pSchedules ps WHERE ps.Assmt_ID = pAssessments.Assmt_ID AND ps.Status = 0 AND ps.Supervision = 1 AND ps.Visit_Date < now() ORDER BY ps.Visit_Date DESC LIMIT 0,1) AS visits_scheduledold, pAuths.Skill,pEmployee.Emp_Last,pEmployee.Emp_First,pEmployee.Skill_1,pEmployee.Skill_2, pEmployee.Skill_3 FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAuths ON pSchedules.Auth_ID = pAuths.Auth_ID AND pAuths.Status =0 JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status =0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID WHERE pPatients.Status < 2 AND pSchedules.Supervision = 1 AND pSchedules.status = 0 GROUP BY pSchedules.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View supervision non-compliance"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules pSchedules JOIN #Request.prefix_db_agency#.pPatients pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID WHERE pSchedules.Status = 0 AND pPatients.Status < 2 AND pEmployee.Status = 0 AND pSchedules.pgnotesdraft = 2 AND pSchedules.Missed = 0 AND pSchedules.Visit_Date <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND pSchedules.QA_View IN (0, 2) GROUP BY pSchedules.Schedule_ID SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View progress notes to review"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pAssessments pAssessments JOIN #Request.prefix_db_agency#.pPatients pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID JOIN #Request.prefix_db_agency#.pSchedules pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID JOIN #Request.prefix_db_agency#.pAdmit pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID WHERE pAssessments.Status = 0 AND pPatients.Status < 2 AND pSchedules.Status = 0 AND pSchedules.Emp_ID != 0 AND pSchedules.pgnotesdraft != 2 AND pSchedules.Visit_Date <= NOW() SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Patient_ID = AND Header = AND Daily_QA_Date = AND status = 0 View progress notes to approve"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID, Patient_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , ) SELECT DISTINCT IFNULL(pAssessments.Assmt_ID,0) AS Assmt_ID, pAssessments.F177 AS CertStart, pAssessments.F25 AS Admission_Status , pAssessments.F178 AS CertEnd, pAssessments.F26 AS Reason, F197 AS TRANSTATUS, '' AS Assessment_Number, pAssessments.Assmt_ID, pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, ''AS DOC, CASE WHEN pAssessments.F177 < '2020-01-01' THEN "FIRST FINAL" WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN "FIRST FINAL" WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN "SECOND FINAL" ELSE "" END as claim_type, pPatients.Pt_First AS PatientFirst, pPatients.Status AS PatStat, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Missed,pSchedules.Patient_ID, pSchedules.PtPayer_ID AS PAYID, pSchedules.Skill AS Visit_Types, CASE WHEN pSchedules.Followup_visit = 1 THEN pSchedules.Visit_Type ELSE pSchedules.Skill END AS Visit_Type, pAssessments.Admit_ID, pSchedules.Progress_Note, pSchedules.Followup_visit, pSchedules.Initial_visit, pSchedules.Total_Units, '' AS SOUT, pSchedules.PtPayer_ID, pSchedules.Nonbillable,pSchedules.Notes, pSchedules.pgnotesdraft, pEmployee.Emp_Last, pEmployee.Emp_First,pSchedules.Emp_ID,IFNULL(pPayer.timely_filing,0) AS timely_filing, pPayer.Pay_ID AS PPAYID, pPayer.Pay_Name,pPayer.Output AS Output_Type,pAssessments.F306 FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Patient_ID = pSchedules.Patient_ID AND pAssessments.status = 0 JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 JOIN #Request.prefix_db_agency#.pAuths ON pSchedules.Auth_ID = pAuths.Auth_ID AND pAuths.status = 0 JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pAuths.Rev_ID AND pRevenue_Code.status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_Id = pSchedules.Patient_Id AND pPatients.Status IN (0,1,4) JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status < 2 JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID WHERE pSchedules.Visit_Date < Now() AND pSchedules.Invoice_ID = 0 AND pSchedules.Nonbillable = 0 AND pSchedules.status = 0 AND pSchedules.Missed = 0 AND pPatients.Patient_ID IS NOT NULL AND pAssessments.Assmt_ID IS NOT NULL AND pPatients.Loc_ID = GROUP BY pSchedules.Schedule_ID Order by PatientLast, PatientFirst, Visit_Date, CertEnd, Reason, Visit_Type SELECT DISTINCT pSchedules.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pSchedules.Schedule_ID = AND pSchedules.Status = 0 AND pEmployee.Loc_ID = AND pEmployee.Status = 0 AND employee_auth.eaRole LIKE '%Field%' AND employee_auth.eaRole NOT LIKE '%Project Manager%' AND employee_auth.eaRole NOT LIKE '%Quality%' AND pSchedules.Emp_ID != 0 SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Schedule_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring visits"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Schedule_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , , ) SELECT DISTINCT pEmployee.Emp_ID, pEmployee.Emp_First, pEmployee.Emp_Last FROM #Request.prefix_db_lookup#.pEmployee pEmployee INNER JOIN #Request.prefix_db_agency#.employee_auth ON pEmployee.Emp_ID = employee_auth.eaID WHERE pEmployee.Loc_ID = AND pEmployee.Status = 0 AND (employee_auth.eaRole LIKE '%Project Manager%' OR employee_auth.eaRole LIKE '%Clinical Quality Assurance%') SELECT COUNT(*) AS totalrecordCount FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Schedule_ID = AND Header = AND Daily_QA_Date = AND status = 0 View expiring visits"> INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Assmt_ID, Emp_ID, Patient_ID, Schedule_ID, Pt_Last, Pt_First, Emp_First, Emp_Last, Header, Description, Created_Date, status) VALUES ( , , , , , , , , , , , , ) SELECT Email_Daily_QA.*, pEmployee.Emp_Email FROM #Request.prefix_db_agency#.Email_Daily_QA Email_Daily_QA LEFT JOIN #Request.prefix_db_lookup#.pEmployee pEmployee ON pEmployee.Emp_ID = Email_Daily_QA.Emp_ID WHERE Email_Daily_QA.Header IN ( 'incomplete assessment', 'orphan symptoms to review', 'medications to review', 'missed visit communication', 'missed visit needs documentation', 'unread communication', 'late progress note', 'procedure needs explanation', 'procedure QA', 'incomplete careplan', 'VBP Inconsistencies', 'Medications Need Reconciliation', 'Missing OASIS', 'Authorizations required', 'Frequency Non-Compliance', 'Missing 485', 'Medication Updates Needed', 'Coding Updates Needed', 'Disabled Goals Need Approval', 'Discharges to Review', 'Missing Eligibility', 'Pending Admissions', 'New Orders Need 487', 'Un-Sent Orders', 'Un-Signed Orders', 'Expiring Eligibility', 'Expiring Authorizations', 'Expiring OASIS/Evaluations', 'Supervision non-Compliance', 'PN to Review', 'PN to Approve', 'Expiring Visits' ) AND Email_Daily_QA.Daily_QA_Date = AND Email_Daily_QA.status = 0 ORDER BY Email_Daily_QA.Header, Email_Daily_QA.Pt_Last, Email_Daily_QA.Pt_First SELECT DISTINCT Header FROM getDailyQAEmail ORDER BY Header Environment: #HTMLEditFormat(environmentName)#
Hello Melissa/Janice,

The following QA items were generated for #DateFormat(currentDate,"mmmm dd, yyyy")# for #Agencylist.Agency_Name# (#Agencylist.Agency_Login#).

#HTMLEditFormat(headerValue)#

SELECT * FROM getDailyQAEmail WHERE Header = ORDER BY Pt_Last, Pt_First

Patient	Description	Assigned Employee
#HTMLEditFormat(patientLast)# , #HTMLEditFormat(patientFirst)#	#Description#	#HTMLEditFormat(empLastName)# , #HTMLEditFormat(empFirstName)#

This is an automated system notification from MyHomeCareBiz.com. Therefore, there is no unsubscribe option.

An error occurred in cron_email_daily_QA.cfm for agency #Agencylist.Agency_ID# (#Agencylist.Agency_Name# / #Agencylist.Agency_Login#).

Message: #cfcatch.message#
Detail: #cfcatch.detail#
Stacktrace:


#HTMLEditFormat(stackItem.template)# (line #stackItem.line#)

#errorDetails#