SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA like '%agency_%' group by TABLE_SCHEMA DELETE FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Header IN ('Incomplete OASIS/Evaluations','vbp inconsistencies') 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, pAdmit.M1100_PTNT_LVG_STUTN, pAssessments.F197 AS Transmission_status, pPatients.Pt_First, pPatients.Pt_Last, Pt_Middle,pAssessments.*, (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, (SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 = 'PT Adult Evaluation' AND pass.Admit_ID = pAssessments.Admit_ID AND pass.Status = 0 AND pass.F25 = 1 ORDER BY pass.F176 DESC LIMIT 0,1) AS PTadult_Assmt_ID, (SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 = 'OT Adult Evaluation' AND pass.Admit_ID = pAssessments.Admit_ID AND pass.Status = 0 AND pass.F25 = 1 ORDER BY pass.F176 DESC LIMIT 0,1) AS OTadult_Assmt_ID, (SELECT Assmt_ID FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 = 'ST Adult Evaluation' AND pass.Admit_ID = pAssessments.Admit_ID AND pass.Status = 0 AND pass.F25 = 1 ORDER BY pass.F176 DESC LIMIT 0,1) AS STadult_Assmt_ID 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 WHERE pPatients.Loc_ID = '#agency_id#' AND pAssessments.Status = 0 AND pAssessments.F25 = 1 AND LEFT(pAssessments.F26,1) IN (1,4) AND pAssessments.F178 >= #NOW()# AND pAssessments.Admit_ID NOT IN (SELECT Admit_ID FROM #Request.prefix_db_agency#.pAssessments WHERE (F26 like '9%' OR F26 like '7%' OR pAssessments.F26 like '8%' AND F176 < #Now()#) AND Status =0 AND F25 = 1) GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_First,pPatients.Patient_ID,Assessment_Reason_M0100 ASC DELETE FROM #Request.prefix_db_agency#.pVBP_Inconsist INSERT INTO #Request.prefix_db_agency#.pVBP_Inconsist (Patient_ID, Assmt_ID, Date_Created, Created_By, Date_Change, Change_by) VALUES( , , , , , ) SELECT LAST_INSERT_ID() as VBP_ID SELECT * FROM #Request.prefix_db_agency#.pAssessments WHERE Status = 0 AND Assmt_ID = '#PTadult_Assmt_ID#' UPDATE #Request.prefix_db_agency#.pVBP_Inconsist SET SOC_M1700_1 = , Eval_M1700_1 = , SOC_M2102_1 = , Eval_M2102_1 = , Message = , Date_Change = WHERE VBP_ID = AND Status = 0 SELECT * FROM #Request.prefix_db_agency#.pAssessments WHERE Status = 0 AND Assmt_ID = '#OTadult_Assmt_ID#' UPDATE #Request.prefix_db_agency#.pVBP_Inconsist SET SOC_M1700_1 = , Eval_M1700_2 = , SOC_M2102_1 = , Eval_M2102_2 = , Message = , Date_Change = WHERE VBP_ID = AND Status = 0 SELECT * FROM #Request.prefix_db_agency#.pAssessments WHERE Status = 0 AND Assmt_ID = '#STadult_Assmt_ID#' UPDATE #Request.prefix_db_agency#.pVBP_Inconsist SET SOC_M1700_3 = , Eval_M1700_3 = , SOC_M2102_3 = , Eval_M2102_3 = , Message = , Date_Change = WHERE VBP_ID = AND Status = 0 SELECT * FROM #Request.prefix_db_lookup#.VBP_Analysis WHERE LEFT(M1700,1) = '#M1700_SOC#' AND LEFT(M2102d,1) = '#M2102F_SOC#' AND MorV like 'risk' AND M1100 like '%#M1100_PTNT_LVG_STUTN#%' INSERT INTO #Request.prefix_db_agency#.pVBP_Inconsist (Patient_ID, Assmt_ID, Date_Created, Created_By, Date_Change, Change_by) VALUES( , , , , , ) SELECT LAST_INSERT_ID() as VBP_ID UPDATE #Request.prefix_db_agency#.pVBP_Inconsist SET SOC_M1700_1 = , SOC_M2102_1 = , Message = , Date_Change = WHERE VBP_ID = AND Status = 0 SELECT * FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_lookup#.VBP_Analysis ON VBP_Analysis.Skill = pAssessments.F27 WHERE pAssessments.F26 = '#assesments#' AND pAssessments.Admit_ID = '#Admit_ID#' AND VBP_Analysis.M1100 like '%#M1100_PTNT_LVG_STUTN#%' AND LEFT(VBP_Analysis.M1700,1) = CASE WHEN LEFT(pAssessments.F26,2) = 'OT' THEN LEFT(IFNULL(pAssessments.F38,0),1) WHEN pAssessments.F26 = 'SN Adult Evaluation' THEN LEFT(IFNULL(pAssessments.F47,0),1) ELSE LEFT(IFNULL(pAssessments.F34,0),1) END AND LEFT(VBP_Analysis.M2102d,1) = CASE WHEN LEFT(pAssessments.F26,1) IN ('1','4') THEN LEFT(IFNULL(pAssessments.F72,0),1) WHEN pAssessments.F26 = 'SN Adult Evaluation' THEN LEFT(IFNULL(pAssessments.F49,0),1) ELSE LEFT(IFNULL(pAssessments.F240,0),1) END AND VBP_Analysis.MorV IN ('answers incompatible - please correct','CRITICAL SAFETY RISK') AND pAssessments.Status = 0 ORDER BY pAssessments.F177 DESC UPDATE #Request.prefix_db_agency#.pVBP_Inconsist SET SOC_M1700_1 = , SOC_M2102_1 = , Eval_M1700_1 Eval_M1700_2 Eval_M1700_3 = , Eval_M2102_1 Eval_M2102_2 Eval_M2102_3 = , Message = , Date_Change = WHERE VBP_ID = AND Status = 0 DELETE FROM #Request.prefix_db_agency#.pVBP_Inconsist WHERE SOC_M1700_1 IS NULL AND Eval_M1700_1 IS NULL AND SOC_M1700_2 IS NULL AND Eval_M1700_2 IS NULL AND SOC_M1700_3 IS NULL AND Eval_M1700_3 IS NULL AND SOC_M2102_1 IS NULL AND Eval_M2102_1 IS NULL AND SOC_M2102_2 IS NULL AND Eval_M2102_2 IS NULL AND SOC_M2102_3 IS NULL AND Eval_M2102_3 IS NULL SELECT pPatients.Patient_ID,pPatients.Pt_Last, pPatients.Pt_First, pPatients.Pt_Middle,pPatients.Pt_Street, pPatients.Pt_City, pPatients.Pt_State,pPatients.Pt_Zip, pPatients.Pt_Phone, pAdmit.M0030_START_CARE_DT ,pAssessments.F26, pAssessments.Admit_ID, pAssessments.Assmt_ID, pAdmit.M1100_PTNT_LVG_STUTN, pEmployee.Emp_First, pEmployee.Emp_Last,IFNULL(pEmployee.Emp_ID,0) AS Emp_ID, pVBP_Inconsist.* 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 < 1 AND pAdmit.Status = 0 AND pAssessments.Status = 0 AND pAssessments.F178 >= NOW() Group by pVBP_Inconsist.VBP_ID ORDER BY pPatients.Pt_Last,pPatients.Patient_ID INSERT INTO #Request.prefix_db_agency#.Email_Daily_QA (Daily_QA_Date, Emp_ID,Assmt_ID, Patient_ID, Header,Pt_Last,Pt_First, Emp_First,Emp_Last, Description, Created_Date) VALUES ( '#currentdate#','#Emp_ID#',#Assmt_ID#, #Patient_ID#,'vbp inconsistencies', '#Pt_Last#','#Pt_First#', '#Emp_First#','#Emp_Last#','vbp inconsistencies', #NOW()#) SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, IFNULL(pEmployee.Emp_ID,0) AS Emp_ID, pEmployee.Emp_Last, pEmployee.Emp_First, (SELECT py.Pay_Name FROM #Request.prefix_db_agency#.pPayer py JOIN #Request.prefix_db_agency#.pPtPayer ptpay ON py.Pay_ID = ptpay.Payer_ID AND ptpay.Status = 0 WHERE pPatients.Patient_ID = ptpay.Patient_ID ORDER BY ptpay.Payer_Responsibility ASC LIMIT 1) AS payername, ( 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, pAssessments.Assmt_ID,pAssessments.* 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 pAdmit.Patient_ID = pPatients.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 WHERE pAssessments.status = 0 AND pPatients.Status = 0 AND (pAssessments.F25 IS NULL OR pAssessments.F25 = '0') AND (pAssessments.F176 < '#currentdate#') ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_Last ASC, pPatients.Pt_First ASC, pEmployee.Emp_Last ASC, pEmployee.Emp_First ASC SELECT pPatients.Patient_ID,pPatients.Pt_First,pPatients.Pt_Last,pSchedules.Visit_Date,pSchedules.Skill,pEmployee.Emp_First,pEmployee.Emp_Last,pSchedules.*,pUnstable.Description FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pUnstable on pUnstable.Schedule_ID = pSchedules.Schedule_ID WHERE pSchedules.Status = 0 AND pPatients.Loc_ID = '#agency_id#' AND pPatients.Status < 2 AND pEmployee.Status = 0 AND pSchedules.pgnotesdraft = 2 AND pSchedules.Missed = 0 AND pSchedules.QA_View IN (0,2) AND pSchedules.Visit_Date <= '#DATEFORMAT(now(),"yyyy-mm-dd")#' GROUP BY pSchedules.Schedule_ID ORDER BY pPatients.Pt_First ASC SELECT pCareplan.Description,pCareplan.Notes AS careplan_notes, pPatients.Patient_ID,pPatients.Pt_First,pPatients.Pt_Last,pSchedules.Visit_Date,pSchedules.Skill, pEmployee.Emp_First,pEmployee.Emp_Last,pSchedules.*, pSched_Procedure.Notes as Proc_Notes,pProgress.Notes AS pg_notes,"" As Performed, pProgress.Progress_ID,pProgress.YesOrNo FROM #Request.prefix_db_agency#.pSched_Procedure JOIN #Request.prefix_db_agency#.pSchedules ON pSched_Procedure.Schedule_ID = pSchedules.Schedule_ID JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_agency#.pCareplan ON pCareplan.Careplan_ID = pSched_Procedure.Careplan_ID AND pCareplan.Status = 0 JOIN #Request.prefix_db_agency#.pProgress ON pProgress.Schedule_ID = pSchedules.Schedule_ID AND pProgress.Status =0 AND pCareplan.Careplan_ID = pProgress.Careplan_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID WHERE pSchedules.Status = 0 AND pPatients.Loc_ID = '#agency_id#' AND pPatients.Status < 2 AND pEmployee.Status = 0 AND pSched_Procedure.Status = 0 AND pSchedules.pgnotesdraft = 2 AND pSchedules.Visit_Date >= '2024-07-23' AND pSchedules.QA_Proc != 1 GROUP BY pSched_Procedure.Careplan_ID ORDER BY pPatients.Pt_First ASC SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Missed, pSchedules.Skill AS Type, pSchedules.Visit_Type, pSchedules.Progress_Note, pSchedules.pgnotesdraft, pSchedules.Pay_ID, 0 AS No_Days_Progress, "" AS Note_QA , pEmployee.Emp_ID, pEmployee.Emp_Last, pEmployee.Emp_First, pAssessments.Assmt_ID, pAssessments.F26 FROM #Request.prefix_db_agency#.pAssessments, #Request.prefix_db_agency#.pPatients, #Request.prefix_db_agency#.pSchedules, #Request.prefix_db_lookup#.pEmployee WHERE pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pAssessments.status = 0 AND pPatients.Patient_ID = pSchedules.Patient_ID AND pPatients.Status < 2 AND pSchedules.status= 0 AND pSchedules.Emp_ID != 0 AND pSchedules.pgnotesdraft != 2 AND pSchedules.Visit_Date <= now() AND pSchedules.Emp_ID = pEmployee.Emp_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC, pEmployee.Emp_Last ASC, pEmployee.Emp_First ASC, pSchedules.Skill ASC, pSchedules.Visit_Date ASC UPDATE #Request.prefix_db_lookup#.Agency SET VBP_count = #VBP_count#, Incomplete_OASIS = #Incomplete_OASIS#, pgnotes_require = #pgnotes_require#, schproc_require = #schproc_require#, unapprove_visits = #unapprove_visits# WHERE Agency_ID = #agency_id#