SELECT COUNT(si.Schedule_ID) as TotalVisits
,si.PtPayer_ID AS PAYID, si.Visit_Date, '' AS SOUT
,an.Assmt_ID
,a.Wage_Index
,py.Pay_ID AS PPAYID, py.Pay_Name, py.Output AS POUT
FROM #Request.prefix_db_agency#.pSchedules si
JOIN #Request.prefix_db_agency#.pAssessments an on an.Assmt_ID = si.Assmt_ID
JOIN #Request.prefix_db_agency#.pPatients p on p.Patient_ID = si.Patient_ID
JOIN #Request.prefix_db_lookup#.Agency a on a.Agency_ID = p.Loc_ID
JOIN #Request.prefix_db_agency#.pPtPayer pt on si.PtPayer_ID = pt.PtPayer_ID
JOIN #Request.prefix_db_agency#.pPayer py on pt.Payer_ID = py.Pay_ID
WHERE p.Status < 2
AND si.Visit_Date < Now()
AND si.status = 0
AND si.Nonbillable = 0
AND si.Missed <> 1
AND si.Invoice_ID = 0
AND an.status < 2
GROUP BY pt.Payer_ID
ORDER BY py.Pay_Name