SELECT pPatients.Patient_ID, pPatients.Pt_Last, pPatients.Pt_First, pEmployee.Emp_Last, ( 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, (SELECT count(*) FROM #Request.prefix_db_agency#.pCMS4857 WHERE pCMS4857.Assmt_ID = pAssessments.Assmt_ID AND pCMS4857.Status = 0 AND (pCMS4857.Form_Type="Form 485" OR pCMS4857.Form_Type="Form 487") AND (pCMS4857.Form_Status = 'Sent for E-Signing' OR pCMS4857.Form_Status = 'Sent via Fax' OR pCMS4857.Form_Status IS NULL)) AS unsigned_orders, (SELECT count(*) FROM #Request.prefix_db_agency#.pSchedules ps WHERE ps.Assmt_ID = pSchedules.Assmt_ID AND ps.Status = 0 AND DATEDIFF(ps.Visit_Date,pAssessments.F177) < 30 AND DATEDIFF(ps.Visit_Date,pAssessments.F177) >= 0 AND Nonbillable = 0 AND pgnotesdraft !=2 AND Followup_visit != 1 AND Missed = 0) AS first_pgnotes, (SELECT count(*) FROM #Request.prefix_db_agency#.pSchedules ps WHERE ps.Assmt_ID = pSchedules.Assmt_ID AND ps.Status = 0 AND DATEDIFF(ps.Visit_Date,pAssessments.F177) >= 30 AND Nonbillable = 0 AND pgnotesdraft !=2 AND Followup_visit != 1 AND Missed = 0) AS second_pgnotes, 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, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN pAssessments.F177 WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) ELSE pAssessments.F177 END as start_date, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 29 DAY) WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 59 DAY) ELSE pAssessments.F178 END as end_date, CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) <= 30 THEN true WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) > 30 AND DATEDIFF(now(),pAssessments.F177) >= 30 THEN true WHEN pAssessments.F178 <= NOW() THEN TRUE ELSE false END as bill,pAssessments.F197,pAssessments.F197 AS TranStatus, CASE WHEN (pSchedules.Followup_visit = "1" ) THEN (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 = "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 0 END ) AND pAss.Status = 0 AND pSchedules.Visit_Date = pAss.F176 ORDER BY F25 ASC LIMIT 0,1) ELSE 1 END AS assessmentscompleted, pPayer.Pay_Name, pSchedules.Schedule_ID,pSchedules.PtPayer_ID, pSchedules.Nonbillable,pSchedules.Assmt_ID, pSchedules.Visit_Date, pSchedules.StartTime, pSchedules.EndTime,pSchedules.Visit_Type, pSchedules.Total_Units, pSchedules.Rev_ID, '' AS Total_EClaim_old, CASE WHEN pRevenue_Code.Measure = 'visit' THEN pRevenue_Code.EClaim_Price ELSE (pSchedules.Total_Units * pRevenue_Code.EClaim_Price) END AS Total_EClaim , (SELECT Phys_verified FROM #Request.prefix_db_lookup#.pPhysicians WHERE pPhysicians.Phys_ID = pAdmit.Render_Phys_ID AND pPhysicians.Loc_ID = '#Agency_ID#' AND Status = 0) AS Phys_verified, pPayer.Output AS Output_Type, '' AS EClaimed, pAssessments.F26 AS Assessment_Reason_M0100, pAssessments.F177, pAssessments.F306, pAssessments.F178, pSchedules.Invoice_ID, pPayer.Pay_ID ,pPtPayer.Payer_ID,pRevenue_Code.Rev_Code, pRevenue_Code.HCPCS,pSchedules.pgnotesdraft FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID AND pPatients.Status IN (#Request.patient_status#) JOIN #Request.prefix_db_lookup#.pEmployee ON pSchedules.Emp_ID = pEmployee.Emp_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status < 2 JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pRevenue_Code ON pSchedules.HCPCS_ID = pRevenue_Code.Rev_CodeID AND pRevenue_Code.Status = 0 WHERE pPayer.Pay_ID = '#url.ID#' -- AND pAssessments.F26 = 1 -- AND pSchedules.Progress_Note = 1 AND (pSchedules.pgnotesdraft = 2 OR pSchedules.Visit_Type IS NOT NULL ) AND pSchedules.Visit_Date < NOW() AND pSchedules.status = 0 AND pSchedules.Missed = 0 AND pSchedules.Invoice_ID = 0 AND pSchedules.Nonbillable = 0 AND pAssessments.Status = 0 -- AND pSchedules.Total_units > 0 AND pPatients.Patient_ID = '#FORM.pid#' AND pSchedules.Visit_Date >= #CreateODBCDate(FORM.df)# AND pSchedules.Visit_Date <= #CreateODBCDate(FORM.dt)# AND pPatients.Patient_ID IS NOT NULL GROUP BY Schedule_ID ORDER BY pAssessments.Record_Creation_Date DESC , Visit_Date DESC