SELECT pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst,
pAssessments.F26, pSchedules.Assmt_ID, pSchedules.Schedule_ID,pSchedules.Visit_Date,pSchedules.Visit_Type,pSchedules.Skill,
pCareplan.Description,pEmployee.Emp_Last,pEmployee.Emp_First,
(SELECT pAss.F25 FROM #Request.prefix_db_agency#.pAssessments pAss
WHERE pAss.Patient_ID = pPatients.Patient_ID
AND (LEFT(pSchedules.Visit_Type,1) = LEFT(pAss.F26,1)
OR ( pAssessments.F26 like pSchedules.Visit_Type)
) AND pAss.Status = 0 LIMIT 0,1)
AS assessments_completeds,
(SELECT pAss.F25 FROM #Request.prefix_db_agency#.pAssessments pAss
WHERE pAss.Patient_ID = pAssessments.Patient_ID
AND pAss.F26 = ( CASE
WHEN (pSchedules.Visit_Type = "1 - SOC - SN" ) THEN '1 - Start of care - further visits planned'
WHEN (pSchedules.Visit_Type = "1 - SOC - PT" ) THEN '1 - Start of care - further visits planned - Physical Therapy'
WHEN (pSchedules.Visit_Type = "1 - SOC - OT" ) THEN '1 - Start of care - further visits planned - Occupational Therapy'
WHEN (pSchedules.Visit_Type = "3 - ROC - SN" || pSchedules.Visit_Type = "3 - ROC - PT") THEN '3 - Resumption of care (after inpatient stay)'
WHEN (pSchedules.Visit_Type = "PT Eval" ) THEN 'PT Adult Evaluation'
WHEN (pSchedules.Visit_Type = "OT Eval" ) THEN 'OT Adult Evaluation'
WHEN (pSchedules.Visit_Type = "ST Eval" ) THEN 'ST Adult Evaluation'
WHEN (pSchedules.Visit_Type = "SN Eval" ) THEN 'SN Adult Evaluation'
WHEN (pSchedules.Visit_Type = "MSW Eval" ) THEN 'MSW Evaluation'
WHEN (pSchedules.Visit_Type = "PT FU Eval" ) THEN 'PT Followup'
WHEN (pSchedules.Visit_Type = "OT FU Eval" ) THEN 'OT Followup'
WHEN (pSchedules.Visit_Type = "ST FU Eval" ) THEN 'ST Followup'
WHEN (pSchedules.Visit_Type = "MSW FU Eval" ) THEN 'MSW Followup'
WHEN (pSchedules.Visit_Type = "SN FU Eval" ) THEN 'SN Followup'
WHEN (pSchedules.Visit_Type = "Hospice Comprehensive" ) THEN 'Hospice Comprehensive'
WHEN (pSchedules.Visit_Type = "SN Hospice" ) THEN 'SN Hospice'
WHEN (pSchedules.Visit_Type = "Chaplain Comprehensive Evaluation" ) THEN 'Chaplain Comprehensive Evaluation'
WHEN (pSchedules.Visit_Type = "Hospice Discharge" ) THEN 'Hospice Discharge'
WHEN (pSchedules.Visit_Type = "4 - Recertification" ) THEN '4 - Recertification (follow-up) reassessment'
WHEN (pSchedules.Visit_Type = "5 - Other follow-up" ) THEN '5 - Other follow-up'
WHEN (pSchedules.Visit_Type = "6 - Transfer no discharge" ) THEN '6 - Transferred to an inpatient facility - patient not discharged from agency'
WHEN (pSchedules.Visit_Type = "7 - Transfer discharged" ) THEN '7 - Transferred to an inpatient facility - patient discharged from agency'
WHEN (pSchedules.Visit_Type = "8 - Death" ) THEN '8 - Death at home'
WHEN (pSchedules.Visit_Type = "9 - Discharge from agency" ) THEN '9 - Discharge from agency'
ELSE 0
END )
AND pAss.Status = 0
ORDER BY F25 ASC LIMIT 0,1) AS assessments_completed
FROM #Request.prefix_db_agency#.pSchedules
LEFT OUTER 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
LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pEmployee.Emp_ID = pSchedules.Emp_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.pSched_Procedure ON pSched_Procedure.Schedule_ID = pSchedules.Schedule_ID AND pSched_Procedure.Status = 0
LEFT OUTER JOIN #Request.prefix_db_agency#.pCareplan ON pCareplan.Careplan_ID = pSched_Procedure.Careplan_ID AND pCareplan.Status = 0
WHERE (pAssessments.status = 0 OR pAssessments.status IS NULL)
AND pPatients.Status = 0
AND pSchedules.status = 0
AND pSchedules.Missed = 0
AND pSchedules.Visit_Date <= '#DateFormat(now(),"yyyy-mm-dd")#'
AND pSchedules.pgnotesdraft != 2
AND pSchedules.Emp_ID = #session.EmployeeID#
GROUp BY pSchedules.Schedule_ID
ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC