SELECT sum(s.Amount) as paid, sum(s.WriteOffAmount) as WriteOffAmount, s.PatientPayer_ID ,s.writeoffamount as wOff, pa.Patient_ID, pa.Last_Name_M0040, pa.First_Name_M0040, pa.Status, DATE_FORMAT(pa.DOB_M0066,'%m/%d/%Y') AS DateOfBirth, p.Invoice_Id, DATE_FORMAT(p.DateSent,'%Y-%m-%d') AS DateSent, DATE_FORMAT(p.DateSent,'%m/%d') AS ds, sum(p.InvoiceTotal) as InvoiceTotal, p.PayerBalance,DATEDIFF(CURDATE(), p.DateSent) AS age, pr.Payer_Id, pr.PayName, pr.Payer_Number from #Request.prefix_db_agency#.Payments s join #Request.prefix_db_agency#.Invoices p on p.Invoice_ID = s.Invoice_ID join #Request.prefix_db_agency#.patient pa on pa.patient_id = p.patient_id join #Request.prefix_db_agency#.Payer pr on pr.payer_id = p.patientpayer_id WHERE p.Deleted IS NULL OR p.Deleted = 0 AND p.InvoiceTotal > 0 AND p.DateSent >= #CreateODBCDate(form.df)# AND p.DateSent <= #CreateODBCDate(form.dt)# and p.Agency_ID = '#session.AgencyID#' AND Amount > 0 AND pr.Payer_Id not in ('2123','2273') group by pr.Payer_Id ORDER BY pr.payname, pr.payer_id, age DESC

All open invoices

Payer

Due

Paid

&df=#form.df#&dt=#form.dt#&Med=yes'> #PayName# : #Payer_Number#

#DollarFormat(Replace(InvoiceTotal, ",", "", "ALL"))#

#DollarFormat(paid)#

Total :

#DollarFormat(Replace(Tot_Due, ",", "", "ALL"))#

#DollarFormat(Tot_Paid)#

There are no Outstanding Invoices associated with your Agency