select * from #Request.prefix_db_lookup#.Agency ORDER BY agency_id DELETE FROM #Request.prefix_db_agency#.pCompliance SELECT *, (SELECT Count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pAdmit.Case_Manager = pCareplan.Created_by AND pCareplan.Status = 0) AS careplancount, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND (LEFT(pAssessments.F26,1) = 1)) AS missedSOCcount, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND (LEFT(pAssessments.F26,1) = 1) AND pAssessments.F178 < NOW() AND pAssessments.Status = 0 AND pAdmit.Status = 0) AS SOCInProgress, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND (LEFT(pAssessments.F26,1) = 1 AND pAssessments.F178 > NOW()) AND pAssessments.Status = 0 AND pAdmit.Status = 0) AS ExpiredSOCcount, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND (LEFT(pAssessments.F26,1) = 4) AND pAssessments.F178 > NOW() AND pAssessments.Status = 0 AND pAdmit.Status = 0) AS RecertInProgress, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = 3 AND pAssessments.status = 0 AND pAdmit.Status = 0) AS missedResumption, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = 4 AND pAssessments.status = 0 AND pAdmit.Status = 0) AS missedRecertcount, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = 4 AND pAssessments.F178 < NOW() AND pAssessments.status = 0 AND pAdmit.Status = 0) AS expiredRecertcount, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '6' AND pAssessments.status = 0 AND pAdmit.Status = 0) AS missed6_Transfer, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '7' AND pAssessments.status = 0 AND pAdmit.Status = 0) AS missed7_Transfer, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '8' AND pAssessments.status = 0 AND pAdmit.Status = 0) AS missed8_death, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '9' AND pAssessments.status = 0 AND pAdmit.Status = 0) AS missed9_discharge, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = 'PT Adult Evaluation' AND DATEDIFF(NOW(),F176) > 30 AND pAdmit.Status = 0) AS PTAdultExpired FROM #Request.prefix_db_agency#.pPatients JOIN #Request.prefix_db_agency#.pAdmit ON pPatients.Patient_ID = pAdmit.Patient_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager WHERE pPatients.Status = 0 AND pAdmit.Status = 0 GROUP BY pPatients.Patient_ID SELECT * FROM #Request.prefix_db_agency#.pAssessments WHERE LEFT(pAssessments.F26,1) IN (6,7,8,9) AND status = 0 GROUP BY Patient_ID INSERT INTO #Request.prefix_db_agency#.pCompliance (Patient_ID, Emp_ID, Report, Link, Age, Date_Create, Created_by, Date_Change, Change_by) VALUES ( #Patient_ID#,#Emp_ID#,'#missed_OASIS#','../dashboard/index.cfm?page=dashboard&cat&rpt=missing_oasis','#diffdays#',NOW(),2,NOW(),2 ) SELECT pAssessments.*, pPatients.Record_Creation_Date AS pat_Datecreate,pPatients.Patient_ID AS Pat_ID, CASE WHEN pEmployee.Emp_ID > 0 THEN pEmployee.Emp_ID ELSE 0 END AS employeeid, (SELECT Count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pAssessments.Assmt_ID = pCareplan.Assmt_ID AND pCareplan.Status = 0 ) AS careplancount FROM #Request.prefix_db_agency#.pPatients LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Patient_ID = pPatients.Patient_ID AND pAssessments.Status = 0 AND LEFT(pAssessments.F26,1) IN (1,4,6,7,8,9) AND now() between pAssessments.F177 AND pAssessments.F178 LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pPatients.Patient_ID = pAdmit.Patient_ID AND pAdmit.Status = 0 LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager AND pEmployee.Status = 0 WHERE pPatients.Status = 0 AND pPatients.Patient_ID NOT IN (#request.dischargepatient_ids#) GROUP BY pPatients.Patient_ID ORDER BY pAssessments.F178 DESC INSERT INTO #Request.prefix_db_agency#.pCompliance (Patient_ID, Emp_ID, Report, Link, Age, Date_Create, Created_by, Date_Change, Change_by) VALUES ( #Pat_ID#,#employeeid#,'no careplan','','#diffdays#',NOW(),2,NOW(),2 ) SELECT *, (SELECT Count(*) FROM #Request.prefix_db_agency#.pCareplan WHERE pAdmit.Case_Manager = pCareplan.Created_by AND pCareplan.Status = 0) AS careplancount, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '6' AND pAssessments.status = 0 ) AS missed6_Transfer, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '7' AND pAssessments.status = 0 ) AS missed7_Transfer, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '8' AND pAssessments.status = 0 ) AS missed8_death, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND LEFT(pAssessments.F26,1) = '9' AND pAssessments.status = 0 ) AS missed9_discharge, (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = 'PT Adult Evaluation' AND DATEDIFF(NOW(),F176) > 30 AND pAssessments.status = 0 ) AS PTAdultExpired , (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = 'PT Adult Evaluation' AND pAssessments.status = 0 ) AS PTAdultEvaluation , (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = 'PT Followup' AND pAssessments.status = 0 ) AS PTFollowup , (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = 'OT Adult Evaluation' AND DATEDIFF(NOW(),F176) > 30 AND pAssessments.status = 0 ) AS OTAdultExpired , (SELECT Count(*) FROM #Request.prefix_db_agency#.pAssessments WHERE pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.Patient_ID = pAssessments.Patient_ID AND pAssessments.F26 = 'OT Followup' AND pAssessments.status = 0 ) AS OTFollowup , (SELECT Count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND pCMS4857.status = 0 AND DATEDIFF(NOW(),pCMS4857.Date_Create) > 14 ORDER BY pCMS4857.Date_Create DESC ) AS no487 FROM #Request.prefix_db_agency#.pPatients JOIN #Request.prefix_db_agency#.pAdmit ON pPatients.Patient_ID = pAdmit.Patient_ID JOIN #Request.prefix_db_agency#.pAssessments ON pAdmit.Admit_ID = pAssessments.Admit_ID JOIN #Request.prefix_db_agency#.pAuths ON pAuths.Assmt_ID = pAssessments.Assmt_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager WHERE pPatients.Status = 0 AND pAdmit.Status = 0 AND pAuths.Status = 0 AND pAuths.Skill = 'SN' AND pAssessments.Status = 0 GROUP BY pPatients.Patient_ID SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Skill AS 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 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.Progress_Note = '0' OR pSchedules.pgnotesdraft < 2) AND pSchedules.Missed = '0' AND pSchedules.Visit_Date <= NOW() AND pSchedules.Emp_ID = pEmployee.Emp_ID GROUP BY pPatients.Patient_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 INSERT INTO #Request.prefix_db_agency#.pCompliance (Patient_ID, Emp_ID, Report, Link, Age, Date_Create, Created_by, Date_Change, Change_by) VALUES ( #Patient_ID#,#Emp_ID#,'missing_note','../dashboard/index.cfm?page=dashboard&cat&rpt=dash_no_notes','#diffdays#',NOW(),2,NOW(),2 ) SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Skill AS Type, pSchedules.Missed, pSchedules.Progress_Note, 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 , pProgress. *,IFNULL(Left(pProblem.Answer,1),2) AS probSeverity,pProgress.Type AS pgtype FROM #Request.prefix_db_agency#.pProgress JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Schedule_ID = pProgress.Schedule_ID JOIN #Request.prefix_db_agency#.pAssessments ON pSchedules.Assmt_ID = pAssessments.Assmt_ID 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 LEFT OUTER JOIN #Request.prefix_db_agency#.pProblem ON pProblem.Problem_ID = pProgress.Careplan_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pWoundsVitals ON pWoundsVitals.ID = pProgress.Careplan_ID WHERE pAssessments.status = 0 AND pPatients.Status < 1 AND pSchedules.status= 0 AND pSchedules.Emp_ID != 0 AND pSchedules.Missed = '0' AND pSchedules.Skill = 'SN' AND pProgress.status= 0 AND pProgress.Comm_ID = 0 AND pProgress.Type IN ('problem','vital') AND (pProgress.Physician_alert = 1 OR pProgress.severity > 1) AND pSchedules.Visit_Date >= '2023-06-14' GROUP BY pProgress.Progress_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 INSERT INTO #Request.prefix_db_agency#.pCompliance (Patient_ID, Emp_ID, Report, Link, Age, Date_Create, Created_by, Date_Change, Change_by) VALUES ( #Patient_ID#,#Emp_ID#,'unstable_symptoms','../dashboard/index.cfm?page=dashboard&cat&rpt=unstable_symptoms','#diffdays#',NOW(),2,NOW(),2 ) SELECT (SELECT V_or_M FROM #Request.prefix_db_lookup#.VBP_Analysis3 WHERE LEFT(SOC_M1700,1) = IFNULL(LEFT(pAssessments.F34,1),0) AND LEFT(SOC_M2102D,1) = IFNULL(LEFT(pAssessments.F73,1),0) AND '1 - Start of care - further visits planned' = pAssessments.F26) AS SOC_MorV, (SELECT V_or_M FROM #Request.prefix_db_lookup#.VBP_Analysis3 WHERE LEFT(SOC_M1700,1) = IFNULL(LEFT(pAssessments.F34,1),0) AND LEFT(SOC_M2102D,1) = IFNULL(LEFT(pAssessments.F73,1),0) AND 'PT Adult Evaluation' = pAssessments.F26) AS PT_MorV, (SELECT V_or_M FROM #Request.prefix_db_lookup#.VBP_Analysis3 WHERE LEFT(SOC_M1700,1) = IFNULL(LEFT(pAssessments.F34,1),0) AND LEFT(SOC_M2102D,1) = IFNULL(LEFT(pAssessments.F73,1),0) AND 'OT Adult Evaluation' = pAssessments.F26) AS OT_MorV,pEmployee.Emp_ID, 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, pAssessments.F177, IFNULL(LEFT(pAssessments.F34,1),0) AS M1700, IFNULL(LEFT(pAssessments.F73,1),0) AS M2102d, IFNULL(LEFT(pAssessments.F82,1),0) AS GG0100A, IFNULL(LEFT(pAssessments.F83,1),0) AS GG0100B, IFNULL(LEFT(pAssessments.F34,1),0) AS M1700_PT, IFNULL(LEFT(pAssessments.F240,1),0) AS M2102d_PT, IFNULL(LEFT(pAssessments.F138,1),0) AS GG0100A_PT, IFNULL(LEFT(pAssessments.F139,1),0) AS GG0100B_PT, IFNULL(LEFT(pAssessments.F38,1),0) AS M1700_OT, IFNULL(LEFT(pAssessments.F240,1),0) AS M2102d_OT, IFNULL(LEFT(pAssessments.F138,1),0) AS GG0100A_OT, IFNULL(LEFT(pAssessments.F139,1),0) AS GG0100B_OT, (SELECT V_or_M FROM #Request.prefix_db_lookup#.VBP_Analysis3 WHERE LEFT(SOC_M1700,1) = IFNULL(LEFT(pAssessments.F34,1),0) AND LEFT(SOC_M2102D,1) = IFNULL(LEFT(pAssessments.F73,1),0) ) AS SOC_MorV, (SELECT count(*) FROM #Request.prefix_db_agency#.pWoundsVitals WHERE pWoundsVitals.Assmt_ID = pAssessments.Assmt_ID AND VitalOrWound = 'wound' AND HealingStage like '3%'AND Status = 0) AS unhealed, pAdmit.M1100_PTNT_LVG_STUTN, pEmployee.Emp_Last , pEmployee.Emp_First 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 pAdmit.Admit_ID = pAssessments.Admit_ID JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pAdmit.Case_Manager WHERE pPatients.Status < 2 AND pAdmit.Status = 0 AND pAssessments.F26 IN ('1 - Start of care - further visits planned','PT Adult Evaluation','OT Adult Evaluation' ) AND pAssessments.Status = 0 Group by pAssessments.Patient_ID ORDER BY pPatients.Pt_Last,pPatients.Patient_ID, FIELD(pAssessments.F26,'1 - Start of care - further visits planned','PT Adult Evaluation','OT Adult Evaluation') ASC SELECT VBP_Analysis3.* FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_lookup#.VBP_Analysis3 ON LEFT(SOC_M1700,1) = LEFT(pAssessments.F34,1) AND LEFT(SOC_M2102D,1) = LEFT(pAssessments.F73,1) WHERE pAssessments.Patient_ID = #Patient_ID# AND pAssessments.status = 0 AND pAssessments.F26 IN ('1 - Start of care - further visits planned') SELECT VBP_Analysis3.* FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_lookup#.VBP_Analysis3 ON LEFT(SOC_M1700,1) = LEFT(pAssessments.F34,1) AND LEFT(SOC_M2102D,1) = LEFT(pAssessments.F240,1) WHERE pAssessments.Patient_ID = #Patient_ID# AND pAssessments.status = 0 AND pAssessments.F26 IN ('PT Adult Evaluation') SELECT VBP_Analysis3.* FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_lookup#.VBP_Analysis3 ON LEFT(SOC_M1700,1) = LEFT(pAssessments.F38,1) AND LEFT(SOC_M2102D,1) = LEFT(pAssessments.F240,1) WHERE pAssessments.Patient_ID = #Patient_ID# AND pAssessments.status = 0 AND pAssessments.F26 IN ('OT Adult Evaluation') '/> PT - ' & getptVBP.V_or_M & ''/> OT - ' & getotVBP.V_or_M & ''/> SELECT Assmt_ID, pAssessments.Admit_ID, F26, F34, F81, F37, F38, F240, F82, F138, F83, F139, F73, F72, F140, F143, F223, IFNULL(LEFT(F34,1),0) AS M1700, CASE WHEN F26 = '1 - Start of care - further visits planned - Physical Therapy' THEN IFNULL(LEFT(F72,1),0) ELSE IFNULL(LEFT(F73,1),0) END AS M2102d, IFNULL(LEFT(F82,1),0) AS GG0100A, IFNULL(LEFT(F83,1),0) AS GG0100B, pAdmit.M1100_PTNT_LVG_STUTN, pAdmit.problem_Intake, pAdmit.Caregiver_commitment, pAdmit.Caregiver_competency FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.status = 0 WHERE pAssessments.Patient_ID = '#Patient_ID#' AND pAssessments.Admit_ID = AND pAssessments.F26 like '1 - Start of care - further visits planned%' AND pAssessments.status = 0 SELECT Assmt_ID, Admit_ID, F37,F34, F38, F240, F82, F138, F83, F139, F73, F102, F111,F112,F113,F114,F115,F116,F117,F118, IFNULL(LEFT(F34,1),0) AS M1700, IFNULL(LEFT(F240,1),0) AS M2102d, IFNULL(LEFT(F138,1),0) AS GG0100A, IFNULL(LEFT(F139,1),0) AS GG0100B, IFNULL(LEFT(F102,1),0) AS GOAL FROM #Request.prefix_db_agency#.pAssessments WHERE Patient_ID = '#Patient_ID#' AND Admit_ID = AND F26 like 'PT Adult Evaluation%' AND Status = 0 SELECT Assmt_ID, Admit_ID, F37, F34, F38, F240, F82, F138, F83, F139, F73, F102, F103,F104,F105,F106,F107,F108,F109,F110,F113,F114,F119,F120, IFNULL(LEFT(F38,1),0) AS M1700, IFNULL(LEFT(F240,1),0) AS M2102d, IFNULL(LEFT(F138,1),0) AS GG0100A, IFNULL(LEFT(F139,1),0) AS GG0100B, IFNULL(LEFT(F102,1),0) AS GOAL FROM #Request.prefix_db_agency#.pAssessments WHERE Patient_ID = '#Patient_ID#' AND Admit_ID = AND F26 like 'OT Adult Evaluation%' AND status = 0 SELECT * FROM #Request.prefix_db_lookup#.VBP_Analysis2 WHERE LEFT(SOC,1) = AND (LEFT(PT_Eval,1) = OR PT_Eval IS NULL) AND (LEFT(OT_Eval,1) = OR OT_Eval IS NULL) AND M0_Item = 'M1700' SELECT * FROM #Request.prefix_db_lookup#.VBP_Analysis2 WHERE LEFT(SOC,1) = AND (LEFT(PT_Eval,1) = OR PT_Eval IS NULL) AND (LEFT(OT_Eval,1) = OR OT_Eval IS NULL) AND M0_Item = 'M2102d' Admission Form - 1700 - ' & getSOCVBP_1700.Alert & '' /> Admission Form - M2102d - ' & getSOCVBP_M2102d.Alert & '' /> INSERT INTO #Request.prefix_db_agency#.pCompliance (Patient_ID, Emp_ID, Report, Link, Age, Date_Create, Created_by, Date_Change, Change_by) VALUES ( #Patient_ID#,#Emp_ID#,'#report#','','#diffdays#',NOW(),2,NOW(),2 ) SELECT pCompliance.*,pPatients.Pt_Last,pPatients.Pt_First, pEmployee.Emp_First,pEmployee.Emp_Last,pAdmit.Case_Manager, Emp.Emp_First AS Casemanager_First,Emp.Emp_Last AS Casemanager_Last FROM #Request.prefix_db_agency#.pCompliance JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pCompliance.Patient_ID JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND pAdmit.status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pCompliance.Emp_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee Emp ON Emp.Emp_ID = pAdmit.Case_Manager WHERE pPatients.Status < 2 ORDER BY pPatients.Pt_Last ASC UPDATE #Request.prefix_db_lookup#.Agency SET Alerts = #getcompliance.recordcount# WHERE Agency_ID = #agency_id# SELECT pCompliance.*,pPatients.Pt_Last,pPatients.Pt_First, pEmployee.Emp_First,pEmployee.Emp_Last,pAdmit.Clinical_Manager, Emp.Emp_First AS Casemanager_First,Emp.Emp_Last AS Casemanager_Last FROM #Request.prefix_db_agency#.pCompliance JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pCompliance.Patient_ID JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Patient_ID = pPatients.Patient_ID AND pAdmit.status = 0 JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pCompliance.Emp_ID LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee Emp ON Emp.Emp_ID = pAdmit.Clinical_Manager WHERE pPatients.Status < 2 ORDER BY pPatients.Pt_Last ASC SELECT * FROM #Request.prefix_db_lookup#.pEmployee WHERE status = 0 AND Loc_ID = #agency_id# SELECT * FROM getcomplianceemployee WHERE Emp_ID = #getemployee.Emp_ID# UPDATE #Request.prefix_db_lookup#.pEmployee SET Emp_alerts = #getempalerts.recordcount# WHERE Emp_ID = #Emp_ID#