SELECT pSchedules.Schedule_ID, pSchedules.Visit_Date, pSchedules.Emp_ID, pSchedules.PtPayer_ID AS PAYID, pSchedules.PtPayer_ID, pSchedules.Nonbillable, CASE WHEN pSchedules.Followup_visit = 1 THEN pSchedules.Visit_Type ELSE pSchedules.Skill END AS Visit_Type, pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst, pPayer.Pay_ID AS PPAYID, pPayer.Pay_Name, pPayer.Output AS Output_Type, IFNULL(pPayer.timely_filing, 0) AS timely_filing, IFNULL(pAssessments.Assmt_ID, 0) AS Assmt_ID, IFNULL(pAssessments.Admit_ID, 0) AS Admit_ID, pAssessments.F177 AS CertStart, pAssessments.F178 AS CertEnd, pAssessments.F26 AS Reason, pAssessments.F306 FROM #Request.prefix_db_agency#.pSchedules LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pSchedules.Assmt_ID > 0 AND pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pSchedules.Assmt_ID > 0 AND 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#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = pSchedules.PtPayer_ID AND pPtPayer.status = 0 JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.status = 0 WHERE pPatients.Status IN (#Request.patient_status#) AND pSchedules.Visit_Date < Now() AND pSchedules.status = 0 AND pSchedules.Nonbillable = 0 AND pSchedules.Missed = 0 AND pSchedules.Invoice_ID = 0 AND ( IFNULL(pSchedules.Assmt_ID, 0) = 0 OR ( pAssessments.status = 0 AND pAdmit.status = 0 ) ) GROUP BY pSchedules.Schedule_ID ORDER BY PatientLast, PatientFirst, Visit_Date, CertEnd, Reason, Visit_Type UPDATE #Request.prefix_db_agency#.Dash_emp_count SET status = 1 WHERE status = 0 AND Header = AND Emp_ID IN () INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , ) UPDATE #Request.prefix_db_agency#.Dash_emp_count SET status = 1 WHERE status = 0 AND Header = AND Emp_ID = INSERT INTO #Request.prefix_db_agency#.Dash_emp_count (Daily_QA_Date, Emp_ID, Emp_Count, Header, Created_Date, status) VALUES ( , , , , , )
Expiring Visits
Patient Payer Visit Date Age # of days until expiration
#PatientLast# #PatientFirst# #Pay_Name# #DateFormat(Visit_Date,"mm/dd/yyyy")# - #Schedule_ID# #age# #until_expiration#