SELECT * FROM Agency WHERE Status = 'active' AND Agency_ID = '#URL.Agency_ID#' group by Agency_ID DELETE FROM #Request.prefix_db_agency#.Email_Daily_QA WHERE Header IN ('Report Missed Visits') SELECT pAdmit.*,pPatients.Patient_ID as pat_id, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pReferral.Referral_Name FROM #Request.prefix_db_agency#.pAdmit JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAdmit.Patient_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pReferral ON pAdmit.Referral_ID = pReferral.Referral_ID AND pReferral.status = 0 WHERE pAdmit.Status = 0 AND pPatients.status = 0 AND pAdmit.drafts = 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 pAssessments.F25 = 1) GROUp BY pAdmit.Admit_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT * FROM #Request.prefix_db_agency#.pSchedule_require WHERE ((SOC_SN = 0 AND authSkill = 'SN' AND service = '1 - SOC - SN') OR (SOC_PT = 0 AND authSkill = 'PT' AND service = '1 - SOC - PT') OR (SOC_OT = 0 AND authSkill = 'OT' AND service = '1 - SOC - OT') OR (ROC_SN = 0 AND authSkill = 'SN' AND service = '3 - ROC - SN') OR (ROC_PT = 0 AND authSkill = 'PT' AND service = '3 - ROC - PT') OR (SN_Eval = 0 AND authSkill = 'SN' AND service = 'SN Eval' ) OR (PT_Eval = 0 AND authSkill = 'PT' AND service = 'PT Eval') OR (OT_Eval = 0 AND authSkill = 'OT' AND service = 'OT Eval') OR (ST_Eval = 0 AND authSkill = 'ST' AND service = 'ST Eval') OR (MSW_Eval= 0 AND authSkill = 'MSW' AND service = 'MSW Eval') OR (authassmt_id = 0 AND schedule_count = 0)) AND service != '0' AND query_type = 'Insert auths WITH OASIS/Eval' SELECT * FROM #Request.prefix_db_agency#.pSchedule_require WHERE ( Recert_visits = 0 OR PTfollow_visits = 0 OR OTfollow_visits = 0 OR Resump_visits = 0) AND query_type = 'Insert auths WITH OASIS/Eval' UNION SELECT * FROM #Request.prefix_db_agency#.pSchedule_require WHERE transfer_visits = 0 AND death_visits = 0 AND discharge_visits = 0 AND Resump_visits = 0 AND query_type = 'Missed ResumptionCare' SELECT * FROM #Request.prefix_db_agency#.pSchedule_require WHERE dischargeoasis_count = 0 AND (( total_recertifications > 0 AND recertification_count = 0 ) OR (PT_need = 1 AND PT_summary= 0 AND pt_noauth = 0) OR (OT_need = 1 AND OT_summary= 0 AND ot_noauth = 0) OR (ST_need = 1 AND ST_summary= 0 AND st_noauth = 0) OR (MSW_need = 1 AND MSW_summary= 0 AND msw_noauth = 0) OR schedule_count = 0) AND query_type = 'Insert auths WITHOUT OASIS/Eval' ORDER BY dischargeoasis_count DESC SELECT pPatients.Patient_ID, pPatients.Pt_Last AS Last_Name_M0040, pPatients.Pt_First AS First_Name_M0040, pPatients.Status AS PatStat, "" AS DOC, pEmployee.Emp_Last AS Last_Name, pEmployee.Emp_First AS First_Name, pSchedules.Schedule_ID, pSchedules.PtPayer_ID, "" AS ApproveBill, pSchedules.Nonbillable, pSchedules.Visit_Date, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) <= 30 THEN pAssessments.F177 WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) > 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 31 DAY) ELSE pAssessments.F177 END as start_date, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) <= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) > 30 THEN pAssessments.F178 ELSE pAssessments.F178 END as end_date, pSchedules.Total_Units, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE Assmt_ID = pAssessments.Assmt_ID AND Status = 0 AND (Form_Type="Form 485" OR Form_Type="Form 487") ) AS orders485, (SELECT count(*) FROM #Request.prefix_db_agency#.pSchedules WHERE pSchedules.Status = 0 AND pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pSchedules.Invoice_ID != 0 AND pSchedules.NOA != 0 ) AS invoicecount, "" AS Output_Type, "" AS EClaimed, pSchedules.Skill AS Type,pSchedules.Emp_ID, pAssessments.F26 AS Assessment_Reason_M0100, pAssessments.F177, '' AS TRANSTATUS, pAssessments.F26 AS Reason, "complete" AS Status, 0 AS Assessment_Number, pAssessments.Assmt_ID, pAssessments.F177 AS Certification_Start,pAssessments.F178 AS Certification_End, DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) AS interval_30_day, pAssessments.F178, pAssessments.F300 AS HIPPS_Code, '' AS Homebound_Status, pSchedules.Invoice_ID, 0 AS Invoice_ID2, pPayer.Pay_ID AS Payer_ID, pPayer.Pay_ID AS PAYID, pPayer.Output as PAYOUT, pPayer.Pay_Name, "" AS notes, pPayer.Pay_ID AS PPAYID, pPayer.Pay_Name, pPayer.Output AS POUT,pPayer.Submitter_ID,pPayer.Receiver_ID, pPayer.PayerSource,pPayer.Payer_No,"1" AS Provider_ID 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 pSchedules.Emp_ID = pEmployee.Emp_ID JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID JOIN #Request.prefix_db_agency#.pPtPayer on pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID JOIN #Request.prefix_db_agency#.pPayer on pPtPayer.Payer_ID = pPayer.Pay_ID WHERE pPatients.Status < 2 AND pSchedules.Status = 0 AND pAssessments.Status = 0 AND pSchedules.Missed = '0' AND pPayer.NOA_req = 1 AND pSchedules.Invoice_ID = 0 AND pSchedules.Nonbillable = 0 AND pPtPayer.status = 0 AND pSchedules.NOA = 0 AND pSchedules.Visit_Type like '1%' AND pAssessments.F26 like '1%' GROUP BY pSchedules.Assmt_ID ORDER BY First_Name_M0040,pSchedules.Visit_Date ASC SELECT Event.Event_ID, Event.EventSub_ID, Event.Event_Date, Event.Patient_ID, pPatients.Pt_Last, pEmployee.Emp_ID, pEmployee.Emp_Last, EventSubjects.EventSub_ID, EventSubjects.Subject,Event.Resolved FROM #Request.prefix_db_agency#.Event LEFT OUTER JOIN #Request.prefix_db_agency#.Event_to ON Event_to.Event_ID = Event.Event_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = Event_to.Emp_ID JOIN #Request.prefix_db_agency#.pPatients ON Event.Patient_ID = pPatients.Patient_ID AND pPatients.status IN (0,1,4) JOIN #Request.prefix_db_agency#.EventSubjects ON Event.EventSub_ID = EventSubjects.EventSub_ID WHERE Event.Agency_ID = #agency_id# And Event.Resolved = 'No' AND Event.status = 0 GROUP BY Event.Event_ID ORDER BY Event_Date DESC, Event.Event_ID 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, Pt_Middle,pAssessments.*,pEmployee.Emp_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 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.Loc_ID = '#agency_id#' AND pAssessments.Status = 0 AND pAssessments.F25 = 1 AND LEFT(pAssessments.F26,1) IN (1,9) AND pAssessments.QA_Count != 1 GROUP BY pAssessments.Assmt_ID ORDER BY pPatients.Pt_First,pPatients.Patient_ID,Assessment_Reason_M0100 ASC 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 UPDATE #Request.prefix_db_lookup#.Agency SET pending_admission = #pending_admission#, scheduling_needed = #scheduling_needed#, noa_count = #noa_count#, incident_count = #incident_count#, discharge_review = #discharge_review#, Incomplete_OASIS = #Incomplete_OASIS# 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 pendingadmission SELECT * FROM getnoas WHERE Emp_ID = '#Emp_ID#' SELECT * FROM getincidences WHERE Emp_ID = '#Emp_ID#' SELECT * FROM Getdischarges WHERE Emp_ID = '#Emp_ID#' AND completed_oasis >= 2 AND dicharge_transmitted = 1 AND Assessment_Reason_M0100 like '1%' UPDATE #Request.prefix_db_lookup#.pEmployee SET Emp_pending_admission = #Emp_pending_admission#, Emp_noa_count = #Emp_noa_count#, Emp_incident_count = #Emp_incident_count#, Emp_discharge_review = #Emp_discharge_review# WHERE Emp_ID = #getemployees.Emp_ID#