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, (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 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 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 GROUP BY pPatients.Patient_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC ) AND comm_SNdischarge EQ 0> ) AND comm_PTdischarge EQ 0> ) AND comm_OTdischarge EQ 0> 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,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 LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee on pPhysicians.Emp_ID = pEmployee.Emp_ID WHERE pPatients.Loc_ID = #agency_id# AND pCMS4857.Status = 0 AND (pCMS4857.Form_Status like '%send%' ) AND pPatients.Status < 2 AND pAssessments.Status = 0 GROUP BY pCMS4857.Order_ID ORDER BY pPatients.Pt_Last, pCMS4857.Certification_To SELECT DISTINCT c.Comm_Id, c.CommSub_Id, c.Comm_Date,c.Patient_Id ,cs.CommSub_Id, cs.Subject,c.Employee_ID,c.Memo ,e.Emp_ID, e.Loc_ID, e.Emp_Last, e.Emp_First ,(SELECT Pt_Last FROM #Request.prefix_db_agency#.pPatients WHERE Patient_Id = c.Patient_Id AND status < 2 LIMIT 1) AS Pt_Last ,(SELECT Pt_First FROM #Request.prefix_db_agency#.pPatients WHERE Patient_Id = c.Patient_Id AND status < 2 LIMIT 1 ) AS Pt_First FROM #Request.prefix_db_agency#.Communication c JOIN #Request.prefix_db_agency#.communication_to ct on ct.comm_id = c.Comm_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee e on e.Emp_ID = ct.employee_id AND e.Loc_ID = '#agency_id#' LEFT OUTER JOIN #Request.prefix_db_agency#.CommunicationSubjects cs on cs.CommSub_Id = c.CommSub_Id WHERE c.Agency_ID = '#agency_id#' AND c.Status = 0 AND c.Comm_record_deleted = 0 AND c.Resolved = 'No' AND (cs.Subject like '%Unstable Symptoms%' OR cs.Subject like '%discharge%' OR cs.Subject like 'symptoms with no related diagnosis' OR cs.Subject like 'missed visit' OR cs.Subject like '%orphan symptom report%' OR cs.Subject like 'Med Reconciliation') AND c.send_fax = 0 GROUP BY c.Comm_Id ORDER BY c.Comm_Date DESC,c.cdate_cdarte DESC limit 0,200 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 * FROM #Request.prefix_db_agency#.pVBP_Inconsist WHERE Status = 0 GROUP BY Patient_ID SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pPatients.Status as pStatus, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND Status = 0 AND Form_Type = 'Form 485') AS Not_sendphysician, (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,pEmployee.Emp_Last,pEmployee.Emp_First, pEmployee.Emp_ID, pAssessments.* FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.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#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager WHERE pAssessments.status = 0 And pAssessments.F25 = 1 AND Left(pAssessments.F26,1) IN ('1','4') AND pAssessments.Orphan_Field IN (1) AND pPatients.Med_Recon_Field IN (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 < '#DateFormat(now(),"yyyy-mm-dd")#') AND pSchedules.Visit_Date > pAssessments.F177 GROUP BY pAssessments.Assmt_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, pPatients.Status as pStatus, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND Status = 0 AND Form_Type = 'Form 485') AS Not_sendphysician, (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,pEmployee.Emp_Last,pEmployee.Emp_First,pEmployee.Emp_ID, pAssessments.* FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.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#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID AND pSchedules.status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager 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 pAssessments.Orphan_Field IN (1) AND pPatients.Med_Recon_Field IN (1) 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 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 < '#DateFormat(now(),"yyyy-mm-dd")#') GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC 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, pEmployee.Emp_Last, pEmployee.Emp_First,pEmployee.Emp_ID, 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 on pPhysicians.Emp_ID = pEmployee.Emp_ID WHERE pPatients.Loc_ID = '#agency_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 (pCMS4857.Date_Sent IS NULL OR pCMS4857.Export !=1 ) AND pPatients.Status < 2 AND pAssessments.Status = 0 GROUP BY pCMS4857.Order_ID ORDER BY pPatients.Pt_Last, pCMS4857.Certification_To SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, Eligibility_Detail.Rejection_code,Eligibility_Detail.Error_code, pPtPayer.*, pPayer.Pay_Name, AAA_codes.Reason,Eligibility_Detail.valid_submission FROM #Request.prefix_db_agency#.Eligibility_Detail JOIN #Request.prefix_db_agency#.Eligibility ON Eligibility.Elig_ID = Eligibility_Detail.Elig_ID AND Eligibility.Status = 0 JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = Eligibility.PtPayer_ID AND pPtPayer.Status = 0 JOIN #Request.prefix_db_agency#.pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID AND pPtPayer.Status = 0 LEFT OUTER JOIN #Request.prefix_db_lookup#.AAA_codes ON AAA_codes.AAA_Error_Code = Eligibility_Detail.Error_code AND pPtPayer.Status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = Eligibility.Patient_ID AND pPtPayer.PtPayer_ID = Eligibility.PtPayer_ID AND pPtPayer.Status = 0 WHERE pPatients.Status IN (0,1,4) AND length(Eligibility_Detail.Rejection_code) > 0 AND Eligibility_Detail.Resolved = 0 GROUP BY pPatients.Patient_ID, pPtPayer.PtPayer_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT psvproc.*,pCareplan.Careplan_ID,pPatients.Patient_ID, pPatients.Pt_First,pPatients.Pt_Last, pPathway.Description,pAssessments.F26,pAdmit.M0030_START_CARE_DT, CASE WHEN pAdmit_Svcs.Skill = '1 - SOC - SN' THEN 'SN' WHEN pAdmit_Svcs.Skill = '1 - SOC - PT' THEN 'PT' WHEN pAdmit_Svcs.Skill = '1 - SOC - OT' THEN 'OT' WHEN pAdmit_Svcs.Skill = '3 - ROC - SN' THEN 'SN' WHEN pAdmit_Svcs.Skill = '3 - ROC - PT' THEN 'PT' WHEN pAdmit_Svcs.Skill = '4 - Recertification' THEN 'SN' WHEN pAdmit_Svcs.Skill = 'SN Eval' THEN 'SN' WHEN pAdmit_Svcs.Skill = 'SN FU Eval' THEN 'SN' WHEN pAdmit_Svcs.Skill = 'PT Eval' THEN 'PT' WHEN pAdmit_Svcs.Skill = 'PT FU Eval' THEN 'PT' WHEN pAdmit_Svcs.Skill = 'OT Eval' THEN 'OT' WHEN pAdmit_Svcs.Skill = 'OT FU Eval' THEN 'OT' WHEN pAdmit_Svcs.Skill = 'ST Eval' THEN 'ST' WHEN pAdmit_Svcs.Skill = 'ST FU Eval' THEN 'ST' ELSE 0 END AS Skills FROM #Request.prefix_db_agency#.pAdmit_Svcs_Procs psvproc JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = psvproc.Admit_ID AND pAdmit.Status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAdmit.Patient_ID AND pPatients.Status IN (0,1,4) JOIN #Request.prefix_db_agency#.pAdmit_Svcs ON pAdmit_Svcs.Admit_ID = psvproc.Admit_ID AND pAdmit_Svcs.Status = 0 JOIN #Request.prefix_db_lookup#.pPathway ON pPathway.Item_ID = psvproc.Item_ID AND pPathway.Type = 'procedure' AND (pPathway.Approved IS NULL OR pPathway.Approved = 'Yes') JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Admit_ID = psvproc.Admit_ID AND pAssessments.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pCareplan ON pAssessments.Assmt_ID = pCareplan.Assmt_ID AND pCareplan.Item_ID = psvproc.Item_ID AND pCareplan.Status = 0 AND pCareplan.Type = 'procedure' AND pCareplan.Skill = CASE WHEN pAdmit_Svcs.Skill = '1 - SOC - SN' THEN 'SN' WHEN pAdmit_Svcs.Skill = '1 - SOC - PT' THEN 'PT' WHEN pAdmit_Svcs.Skill = '1 - SOC - OT' THEN 'OT' WHEN pAdmit_Svcs.Skill = '3 - ROC - SN' THEN 'SN' WHEN pAdmit_Svcs.Skill = '3 - ROC - PT' THEN 'PT' WHEN pAdmit_Svcs.Skill = '4 - Recertification' THEN 'SN' WHEN pAdmit_Svcs.Skill = 'SN Eval' THEN 'SN' WHEN pAdmit_Svcs.Skill = 'SN FU Eval' THEN 'SN' WHEN pAdmit_Svcs.Skill = 'PT Eval' THEN 'PT' WHEN pAdmit_Svcs.Skill = 'PT FU Eval' THEN 'PT' WHEN pAdmit_Svcs.Skill = 'OT Eval' THEN 'OT' WHEN pAdmit_Svcs.Skill = 'OT FU Eval' THEN 'OT' WHEN pAdmit_Svcs.Skill = 'ST Eval' THEN 'ST' WHEN pAdmit_Svcs.Skill = 'ST FU Eval' THEN 'ST' ELSE 0 END AND psvproc.Item_ID = pCareplan.Item_ID AND pAdmit_Svcs.Status = 0 WHERE psvproc.Status = 0 AND pCareplan.Careplan_ID IS NULL GROUP BY pPatients.Patient_ID ORDER BY pPatients.Patient_ID, pAssessments.Assmt_ID 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, 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 = '#agency_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 UPDATE #Request.prefix_db_lookup#.Agency SET missing_auth_count = #missing_auth_count#, unsent_orders_count = #unsent_orders_count#, new_orders_need = #new_orders_need# , missing_485_count = #missing_485_count#, usigned_orders_count = #usigned_orders_count#, Reject_Eligi_count = #Reject_Eligi_count#, procdure_omits = #procdure_omits#, disabledgoals_count = #disabledgoals_count# WHERE Agency_ID = #agency_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 Get485List WHERE Emp_ID = '#Emp_ID#' AND new485_require = 0 SELECT * FROM Get485EvalList WHERE Emp_ID = '#Emp_ID#' AND new485_require = 0 SELECT * FROM unsigned_orders WHERE Emp_ID = '#Emp_ID#' UPDATE #Request.prefix_db_lookup#.pEmployee SET Emp_missing_485_count = #Emp_missing_485_count# WHERE Emp_ID = #getemployees.Emp_ID#