SELECT DISTINCT
( SELECT count(*) FROM #Request.prefix_db_agency#.Eligibility
WHERE Patient_ID = pAssessments.Patient_ID
AND PtPayer_ID = pPtPayer.PtPayer_ID
AND Status = 0 ) AS Eligi,
IFNULL(pAssessments.Assmt_ID,0) AS Assmt_ID, pAssessments.F177 AS CertStart,
pAssessments.F25 AS Admission_Status , pAssessments.F178 AS CertEnd,
pAssessments.F26 AS Reason, F197 AS TRANSTATUS, '' AS Assessment_Number, pAssessments.Assmt_ID,
pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, ''AS DOC,
CASE
WHEN pAssessments.F177 < '2020-01-01' THEN "FIRST FINAL"
WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN "FIRST FINAL"
WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN "SECOND FINAL"
ELSE ""
END as claim_type,
pPatients.Pt_First AS PatientFirst, pPatients.Status AS PatStat,
pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Missed,pSchedules.Patient_ID, pSchedules.PtPayer_ID AS PAYID, pSchedules.Skill AS Visit_Types,
CASE
WHEN pSchedules.Followup_visit = 1 THEN pSchedules.Visit_Type
ELSE
pSchedules.Skill
END AS Visit_Type,
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 = "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 = "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 (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 F26
END AS assesments, pAssessments.Admit_ID,pAdmit.Render_Phys_ID,
pSchedules.Progress_Note, pSchedules.Followup_visit, pSchedules.Initial_visit, pSchedules.Total_Units, '' AS SOUT, pSchedules.PtPayer_ID, pSchedules.Nonbillable,pSchedules.Notes, pSchedules.pgnotesdraft,
pEmployee.Emp_Last, pEmployee.Emp_First,pSchedules.Emp_ID,
pPayer.Pay_ID AS PPAYID, pPayer.Pay_Name,pPayer.Output AS Output_Type,pAssessments.F306
FROM #Request.prefix_db_agency#.pSchedules
LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.status = 0
LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0
JOIN #Request.prefix_db_agency#.pAuths ON pSchedules.Auth_ID = pAuths.Auth_ID AND pAuths.status = 0
JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pAuths.Rev_ID AND pRevenue_Code.status = 0
LEFT OUTER JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_Id = pSchedules.Patient_Id
AND pPatients.Status IN (#Request.patient_status#)
LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status < 2
LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0
LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID
WHERE pPayer.Pay_Id =
AND pSchedules.Visit_Date < Now()
AND pSchedules.Invoice_ID = 0
AND pSchedules.Nonbillable = 0
AND pSchedules.status = 0
AND pSchedules.Missed = 0
AND pSchedules.Missed = 0
AND pPatients.Patient_ID IS NOT NULL
AND pAssessments.Assmt_ID IS NOT NULL
GROUP BY pSchedules.Schedule_ID
Order by PatientLast, PatientFirst, Visit_Date, CertEnd, Reason, Visit_Type
Update #Request.prefix_db_agency#.pSchedules
Set Status = 1
WHERE Schedule_ID = #URL.sid#
AND Status = 0
SELECT * FROM #Request.prefix_db_agency#.pAssessments
WHERE status = 0
AND Admit_ID = "#Admit_ID#"
AND F26 = '#assesments#'
AND F176 = '#Visit_Date#'
ORDER BY F176 DESC
SELECT * FROM #Request.prefix_db_agency#.pCMS4857
WHERE Assmt_ID = '#Assmt_ID#'
AND Status = 0
AND (Form_Type="Form 485" OR Form_Type="Form 487")
AND (Form_Status = 'Sent for E-Signing' OR Form_Status = 'Sent via Fax' OR Form_Status IS NULL)
SELECT * FROM #Request.prefix_db_lookup#.pPhysicians
WHERE pPhysicians.Phys_ID = '#Render_Phys_ID#'
AND pPhysicians.Loc_ID = '#session.agencyid#'
AND Status = 0
SELECT * FROM #Request.prefix_db_agency#.pAssessments
WHERE status = 0
AND Admit_ID = "#Admit_ID#"
AND F26 = '#assesments#'
AND F176 = '#Visit_Date#'
ORDER BY F176 DESC