select Agency_Login from #Request.prefix_db_lookup#.Agency where status='active' ArrayAppend(myArr,#Agency_Login#); SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,a.Freq_of_Pain_M0420,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID and (Visit_Type like '%Skilled Nursing%' or Visit_Type like '%SN%') ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and (s.Visit_Type like '%SN%' or s.Visit_Type like '%Skilled Nursing%') and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%pain%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,a.Oral_Meds_M0780,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID and (Visit_Type like '%Skilled Nursing%' or Visit_Type like '%SN%') ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and (s.Visit_Type like '%SN%' or s.Visit_Type like '%Skilled Nursing%') and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%medication%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = d.Assessment_ID and d.M0230_Link is not null and d.ICD like '436%' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%stroke%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = d.Assessment_ID and d.M0230_Link is not null and d.ICD like '491%' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%cop%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = d.Assessment_ID and d.M0230_Link is not null and d.ICD like '428%' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%heart%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,a.Surgical_Wound_Status_M0488,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID and (Visit_Type like '%Skilled Nursing%' or Visit_Type like '%SN%') ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and (s.Visit_Type like '%SN%' or s.Visit_Type like '%Skilled Nursing%') and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%surgical%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Pathway_Details pd,#Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = pd.Assessment_ID and a.Assessment_ID = d.Assessment_ID and (d.Pathway_Type like '%crazy') and d.M0230_Link is not null and s.Progress_Note = 'Yes' and p.Type = 'symptom' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,a.Ambulation_M0700,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID and (Visit_Type like '%PT%' or Visit_Type like '%physical%') ) as PT_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and (s.Visit_Type like '%PT%' or s.Visit_Type like '%physical%') and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%ambul%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,a.Transferring_M0690,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID and (Visit_Type like '%PT%' or Visit_Type like '%physical%') ) as PT_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and (s.Visit_Type like '%PT%' or s.Visit_Type like '%physical%') and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%transfer%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = d.Assessment_ID and d.M0230_Link is not null and d.ICD between '140' and '239' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Pathway_Details pd,#Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and d.Assessment_ID = a.Assessment_ID and a.Assessment_ID = pd.Assessment_ID and (d.Pathway_Type like 'gastr%') and d.M0230_Link is not null and s.Progress_Note = 'Yes' and p.Type = 'symptom' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.Pathway_Details pd, #Request.prefix_db_agency#.Diagnosis d , #Request.prefix_db_agency#.patient pa where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and d.Assessment_ID = a.Assessment_ID and a.Assessment_ID = pd.Assessment_ID and (d.Pathway_Type = "can't chew" or d.Pathway_Type = 'underweight bonafide') and d.M0230_Link is not null and s.Progress_Note = 'Yes' and p.Type = 'symptom'and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = d.Assessment_ID and d.M0230_Link is not null and d.ICD like '599%' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%urinary%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = d.Assessment_ID and d.M0230_Link is not null and d.ICD like '453%' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%deep vein%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Pathway_Details pd,#Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = pd.Assessment_ID and a.Assessment_ID = d.Assessment_ID and pd.Care_Pathway = '34' and d.M0230_Link is not null and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%pressure ulcer%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID ) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Pathway_Details pd,#Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = pd.Assessment_ID and a.Assessment_ID = d.Assessment_ID and pd.Care_Pathway = '39' and d.M0230_Link is not null and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%stasis ulcer%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID SELECT MAX(p.Progress_Details_ID), s.Visit_Type,pa.Last_Name_M0040,pa.First_Name_M0040,p.Patient_ID,MAX(p.severity) as severity,MAX(d.severity) as Diag_sev,a.Start_of_Care_M0030, (select count(Visit_Date) from #Request.prefix_db_agency#.ScheduleItems where Patient_Id = p.Patient_ID) as SN_Visits, a.Start_of_Care_M0030, MAX(s.Visit_Date) as Visit_Date, DATEDIFF(MAX(s.Visit_Date),a.Start_of_Care_M0030) as Diff_visit,1 FROM #Request.prefix_db_agency#.PatientVisitDetails p, #Request.prefix_db_agency#.ScheduleItems s, #Request.prefix_db_agency#.Assessment_New a, #Request.prefix_db_agency#.patient pa, #Request.prefix_db_agency#.Pathway_Details pd,#Request.prefix_db_agency#.Diagnosis d where p.Agency_ID = #myArr[REF]# and p.ScheduleItems_ID = s.ScheduleItems_ID and pa.Patient_ID = p.Patient_ID and pa.status = 'active' and s.Assessment_ID = a.Assessment_ID and a.Assessment_ID = pd.Assessment_ID and a.Assessment_ID = d.Assessment_ID and pd.Care_Pathway = '5' and s.Progress_Note = 'Yes' and p.Type = 'symptom' and p.Description like '%limited care%' and s.Deleted = 0 group by p.Patient_ID order by p.Patient_ID Update #Request.prefix_db_lookup#.Agency a set a.UMsame=#total_same#, a.UMworse=#total_worse#, a.UMbetter=#total_Better# where a.Agency_Login =#myArr[REF]#