select agency_id,Agency_Login,Agency_Name from #Request.prefix_db_lookup#.Agency where status='active' AND Agency_ID = '#url.Agency_ID#' ORDER BY agency_id TRUNCATE TABLE #Request.prefix_db_agency#.assessment_assign INSERT INTO #Request.prefix_db_agency#.assessment_assign ( Emp_ID,Parent_Assmt_ID,Assmt_ID,Schedule_ID,Patient_ID,Visit_Type,F26) SELECT pSchedules.Emp_ID, pAssessments.Assmt_ID, pAssessments.Assmt_ID, pSchedules.Schedule_ID,pSchedules.Patient_ID, pSchedules.Visit_Type,pAssessments.F26 FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.F26 = (CASE WHEN (Visit_Type = "1 - SOC - SN") THEN '1 - Start of care - further visits planned' WHEN (Visit_Type = "1 - SOC - PT" ) THEN '1 - Start of care - further visits planned - Physical Therapy' WHEN (Visit_Type = "1 - SOC - OT" ) THEN '1 - Start of care - further visits planned - Occupational Therapy' WHEN (Visit_Type = "3 - ROC - SN" || Visit_Type = "3 - ROC - PT") THEN '3 - Resumption of care (after inpatient stay)' WHEN (Visit_Type = "PT Eval" ) THEN 'PT Adult Evaluation' WHEN (Visit_Type = "OT Eval" ) THEN 'OT Adult Evaluation' WHEN (Visit_Type = "ST Eval" ) THEN 'ST Adult Evaluation' WHEN (Visit_Type = "SN Eval" ) THEN 'SN Adult Evaluation' WHEN (Visit_Type = "MSW Eval" ) THEN 'MSW Evaluation' WHEN (Visit_Type = "PT FU Eval" ) THEN 'PT Followup' WHEN (Visit_Type = "OT FU Eval" ) THEN 'OT Followup' WHEN (Visit_Type = "ST FU Eval" ) THEN 'ST Followup' WHEN (Visit_Type = "MSW FU Eval" ) THEN 'MSW Followup' WHEN (Visit_Type = "SN FU Eval" ) THEN 'SN Followup' WHEN (Visit_Type = "Hospice Comprehensive" ) THEN 'Hospice Comprehensive' WHEN (Visit_Type = "SN Hospice" ) THEN 'SN Hospice' WHEN (Visit_Type = "Chaplain Comprehensive Evaluation" ) THEN 'Chaplain Comprehensive Evaluation' WHEN (Visit_Type = "Hospice Discharge" ) THEN 'Hospice Discharge' WHEN (pSchedules.Visit_Type = "4 - Recertification" ) THEN '4 - Recertification (follow-up) reassessment' WHEN (pSchedules.Visit_Type = "4 - Recertification PT" ) THEN '4 - Recertification (follow-up) reassessment - PT' WHEN (pSchedules.Visit_Type = "4 - Recertification OT" ) THEN '4 - Recertification (follow-up) reassessment - OT' WHEN (Visit_Type = "5 - Other follow-up" ) THEN '5 - Other follow-up' WHEN (Visit_Type = "6 - Transfer no discharge" ) THEN '6 - Transferred to an inpatient facility - patient not discharged from agency' WHEN (Visit_Type = "7 - Transfer discharged" ) THEN '7 - Transferred to an inpatient facility - patient discharged from agency' WHEN (Visit_Type = "8 - Death" ) THEN '8 - Death at home' WHEN (Visit_Type = "9 - Discharge from agency" ) THEN '9 - Discharge from agency' ELSE 0 END) AND pAssessments.F176 = pSchedules.Visit_Date JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 WHERE pAssessments.Status = 0 AND pSchedules.status = 0 AND pAssessments.Patient_ID = pSchedules.Patient_ID AND pSchedules.Followup_visit = 1 AND pAdmit.Admit_ID NOT IN (SELECT pas.Admit_ID FROM #Request.prefix_db_agency#.pAssessments pas WHERE (pas.F26 like '9%' OR pas.F26 like '7%' OR pas.F26 like '8%') AND pas.F176 < now() AND pas.Status =0 AND pas.F25 = 1) AND pSchedules.Missed = 0 GROUp BY pSchedules.Schedule_ID UPDATE #Request.prefix_db_agency#.assessment_assign aa INNER JOIN ( SELECT aa2.assign_id, COALESCE( ( SELECT p.Assmt_ID FROM #Request.prefix_db_agency#.pAssessments p INNER JOIN #Request.prefix_db_agency#.pPatients pt ON pt.Patient_ID = p.Patient_ID AND pt.status IN (0, 1, 4) WHERE p.Admit_ID = child.Admit_ID AND p.Status = 0 AND LEFT(p.F26, 1) IN ('1', '4') AND p.F177 IS NOT NULL AND DATE(p.F177) <= DATE(child.F176) AND (p.F178 IS NULL OR DATE(p.F178) >= DATE(child.F176)) ORDER BY p.F177 DESC LIMIT 1 ), ( SELECT p.Assmt_ID FROM #Request.prefix_db_agency#.pAssessments p WHERE p.Admit_ID = child.Admit_ID AND p.Status = 0 AND p.F26 LIKE '%Adult%' ORDER BY p.Record_Creation_Date ASC LIMIT 1 ) ) AS ParentAssmtID FROM #Request.prefix_db_agency#.assessment_assign aa2 INNER JOIN #Request.prefix_db_agency#.pAssessments child ON child.Assmt_ID = aa2.Assmt_ID AND child.Status = 0 INNER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = child.Admit_ID AND pAdmit.status = 0 WHERE (aa2.F26 LIKE '%Adult%' OR aa2.F26 LIKE '% Followup%') ) parentmap ON parentmap.assign_id = aa.assign_id SET aa.Parent_Assmt_ID = parentmap.ParentAssmtID WHERE parentmap.ParentAssmtID IS NOT NULL