SELECT * FROM Agency WHERE Status = 'active' AND Agency_ID = '#URL.Agency_ID#' group by Agency_ID 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 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 IN (0,1,4) AND pEmployee.Status = 0 AND pSched_Procedure.Status = 0 AND pSchedules.pgnotesdraft = 2 AND pSchedules.Visit_Date >= '2024-07-23' AND pSchedules.QA_Proc = 2 GROUP BY pSched_Procedure.Careplan_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 IN (0,1,4) AND pEmployee.Status = 0 AND pSched_Procedure.Status = 0 AND pSchedules.pgnotesdraft = 2 AND pSchedules.Visit_Date >= '2024-07-23' AND pSchedules.QA_Proc = 2 GROUP BY pSched_Procedure.Careplan_ID ORDER BY pPatients.Pt_First ASC SELECT pPatients.Patient_ID,pPatients.Pt_First,pPatients.Pt_Last,pSchedules.Visit_Date,pSchedules.Skill, pSchedules.hi_risk, 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 LEFT OUTER JOIN #Request.prefix_db_agency#.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 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 IN (0,1,4) AND pEmployee.Status = 0 AND pSchedules.pgnotesdraft = 2 AND pSchedules.Missed = 0 AND pSchedules.QA_View IN (2) AND pSchedules.Visit_Date <= '#DATEFORMAT(now(),"yyyy-mm-dd")#' GROUP BY pSchedules.Schedule_ID ORDER BY pPatients.Pt_First ASC today = now(); dayOfWeek = dayOfWeek(today); daysSinceSunday = dayOfWeek - 1; startOfWeek = dateAdd("d", -daysSinceSunday, today); 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, (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,pSchedules.Emp_ID 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 JOIN #Request.prefix_db_agency#.pSchedules on pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 AND LEFT(pp.M0150,1) IN ('1','2') JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID WHERE pPatients.Status < 1 AND Week_Start <= '#DateFormat(startOfWeek, "yyyy-mm-dd")#' 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 <= #now()# 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) GROUP BY pAuths_weeks.Auths_weeks_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC 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 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 pAssessments.F26 = '1 - Start of care - further visits planned' AND pAuths.Skill = 'SN' AND pAuths.Status = 0 ) AS SOCauth_count, (SELECT count(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = '1 - Start of care - further visits planned' AND pSchedules.Skill = 'SN' AND pSchedules.Status = 0 ) AS SOCschedule_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(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.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 pSchedules.Skill = 'PT' AND pSchedules.Status = 0 ) AS SOCPTschedule_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(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '4' AND pSchedules.Status = 0 ) AS Recertschedule_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(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pSchedules.Skill = 'PT' AND pSchedules.Status = 0 ) AS PTAdultschedule_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(pSchedules.Schedule_ID) FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0 AND pAssessments.F25 = 1 WHERE pPatients.Patient_ID = pAssessments.Patient_ID AND pSchedules.Skill = 'OT' AND pSchedules.Status = 0 ) AS OTAdultschedule_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, pSchedules.Emp_ID 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 JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Patient_ID = pPatients.Patient_ID AND pSchedules.status =0 WHERE pPatients.Status = 0 GROUP BY pPatients.Patient_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC 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_ID, 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 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 SELECT pEmployee.Emp_ID FROM #Request.prefix_db_lookup#.pEmployee JOIN #Request.prefix_db_agency#.employee_auth ON employee_auth.eaID = pEmployee.Emp_ID WHERE pEmployee.Status = 0 AND pEmployee.Loc_ID = '#agency_id#' AND pEmployee.Emp_Type like '%Employee%' AND employee_auth.eaRole like '%Field%' SELECT * FROM GetIncomplete_OASIS WHERE Emp_ID = '#Emp_ID#' AND latest_admit = Admit_ID SELECT * FROM getEmp_Procedure WHERE Emp_ID = '#Emp_ID#' SELECT * FROM GetPgnotes WHERE Emp_ID = '#Emp_ID#' SELECT * FROM nonvisit_compliance WHERE Emp_ID = '#Emp_ID#' SELECT * FROM getmissing_auths WHERE Emp_ID = '#Emp_ID#' SELECT * FROM GetExpiring_auths WHERE Emp_ID = '#Emp_ID#' AND communication_discharge = 0 SELECT * FROM getVBPtotal WHERE Emp_ID = '#Emp_ID#' UPDATE #Request.prefix_db_lookup#.pEmployee SET Emp_incomplete_oasis = #Emp_incomplete_oasis#, Emp_schproc_require = #Emp_schproc_require#, Emp_pgnotes_require = #Emp_pgnotes_require#, Emp_auth_noncomp = #Emp_auth_noncomp#, Emp_missing_auth_count = #Emp_missing_auth_count#, Emp_expiring_auth = #Emp_expiring_auth#, Emp_VBP_count = #Emp_VBP_count# WHERE Emp_ID = #getemployees.Emp_ID#