SELECT MAX(s.Payment_ID) as Payment_ID, (select sum(amount) from #Request.prefix_db_agency#.Payments where invoice_id = p.invoice_id) as paid, (s.WriteOffAmount) as WriteOffAmount, s.PatientPayer_ID , (select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = p.invoice_id) as wOff, p.PayerBalance, pa.Patient_ID, pa.Last_Name_M0040, a.Certification_Start,a.Certification_End, pa.First_Name_M0040, pa.Status, DATE_FORMAT(pa.DOB_M0066,'%m/%d/%Y') AS DateOfBirth, p.Invoice_Id, p.Comment, DATE_FORMAT(p.DateSent,'%Y-%m-%d') AS DateSent, MAX(DATE_FORMAT(s.PaymentDate,'%Y-%m-%d')) AS PaymentDate, DATE_FORMAT(p.DateSent,'%m/%d') AS ds, (p.InvoiceTotal) as InvoiceTotal, si.Total_EClaim,si.EClaimed_Final, si.Output_Type, p.PayerBalance, pr.Payer_Id, pr.PayName, pr.Payer_Number, (SELECT Invoice_Id FROM #Request.prefix_db_agency#.Invoices WHERE Assessment_Tracker_Id = p.Assessment_Tracker_Id AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalBilling from #Request.prefix_db_agency#.Payments s left outer join #Request.prefix_db_agency#.ScheduleItems si on s.Invoice_ID = si.Invoice_ID left outer join #Request.prefix_db_agency#.Invoices p on p.Invoice_ID = si.Invoice_ID left outer join #Request.prefix_db_agency#.patient pa on pa.patient_id = p.patient_id left outer join #Request.prefix_db_agency#.Payer pr on pr.payer_id = p.patientpayer_id left outer join #Request.prefix_db_agency#.Assessment_New a on si.Assessment_ID = a.Assessment_ID WHERE p.Agency_Id = '#session.agencyid#' AND p.Deleted IS NULL OR p.Deleted = 0 AND pr.Payer_id = '#url.pr#' AND p.DateSent >= #CreateODBCDate(url.df)# AND p.DateSent <= #CreateODBCDate(url.dt)# AND Amount > 0 AND p.InvoiceTotal > 0 AND si.Agency_ID = '#session.AgencyID#' Group BY p.Invoice_Id ORDER BY Last_Name_M0040, First_Name_M0040 ASC 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, p.Comment, 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(url.df)# AND p.DateSent <= #CreateODBCDate(url.dt)# AND pr.Payer_id = '2057' and p.Agency_ID = '#session.AgencyID#' AND Amount > 0 group by pr.Payer_Id ORDER BY pr.payname, pr.payer_id, age DESC (select sum(writeoffamount) from #Request.prefix_db_agency#.Payments where invoice_id = p.invoice_id) as wOff, p.PayerBalance, pa.Patient_ID, pa.Last_Name_M0040, a.Certification_Start,a.Certification_End, 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(s.PaymentDate,'%Y-%m-%d') AS PaymentDate, DATE_FORMAT(p.DateSent,'%m/%d') AS ds, (p.InvoiceTotal) as InvoiceTotal, si.Total_EClaim,si.EClaimed_Final, si.Output_Type, p.PayerBalance, pr.Payer_Id, pr.PayName, pr.Payer_Number, (SELECT Invoice_Id FROM #Request.prefix_db_agency#.Invoices WHERE Assessment_Tracker_Id = p.Assessment_Tracker_Id AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalBilling from #Request.prefix_db_agency#.Payments s left outer join #Request.prefix_db_agency#.ScheduleItems si on s.Invoice_ID = si.Invoice_ID left outer join #Request.prefix_db_agency#.Invoices p on p.Invoice_ID = si.Invoice_ID left outer join #Request.prefix_db_agency#.patient pa on pa.patient_id = p.patient_id left outer join #Request.prefix_db_agency#.Payer pr on pr.payer_id = p.patientpayer_id left outer join #Request.prefix_db_agency#.Assessment_New a on si.Assessment_ID = a.Assessment_ID WHERE p.Agency_Id = '#session.agencyid#' AND p.Deleted IS NULL OR p.Deleted = 0 AND pr.Payer_id = '#url.pr#' AND p.DateSent >= #CreateODBCDate(url.df)# AND p.DateSent <= #CreateODBCDate(url.dt)# AND (s.Amount-s.WriteOffAmount)>=0 AND si.Agency_ID = '#session.AgencyID#' AND s.Amount >0 Group BY p.Invoice_ID ORDER BY Last_Name_M0040, First_Name_M0040 ASC ---> Select distinct(Patient_ID) as Patient_ID, Last_Name_M0040,First_Name_M0040 from GetI order by Last_Name_M0040, First_Name_M0040 asc Select * from GetI where Patient_ID = #FORM.Patient_List# Select * from GetI Select * from GetI

Patient : #Last_Name_M0040#, #First_Name_M0040# (#Status#) DOB: #DateOfBirth#

Invoice

Payment Date

Certification

Due

Paid

Write-off

Balance

Comment

Action

Select * from Filter where Patient_ID = #in_Patient_ID#

#Invoice_ID#

#DateFormat(PaymentDate,'mm/dd/yyyy')#

#DateFormat(Certification_Start,'mm/dd/yyyy')#-
#DateFormat(Certification_End,'mm/dd/yyyy')#

#DollarFormat(InvoiceTotal)#

#DollarFormat(paid)#

#DollarFormat(wOff)#

#DollarFormat(InvoiceTotal-paid-wOff)#

Comment #Comment#

Edit

Patient Total :

#DollarFormat(_InvTot)#

#DollarFormat(_PaidAmt)#

#DollarFormat(_WriteOff)#

#DollarFormat(_BalAmt)#

Grand Total :

#DollarFormat(GetEI.InvoiceTotal)# #DollarFormat(GND_InvTot)#

#DollarFormat(GetEI.paid)# #DollarFormat(GND_PaidAmt)#

#DollarFormat(GND_WriteOff)#

#DollarFormat(GND_BalAmt)#