select i.Invoice_Id,i.errors,i.TOB, i.DateSent, i.Output, i.MethodSent, i.Comment, i.InvoiceTotal, i.PayerBalance,i.claim_status,
i.User_defined_Status, i.Assessment_Tracker_Id ,DATEDIFF(CURDATE(), i.DateSent) AS age , p.Patient_Id, p.Status, p.Last_Name_M0040, p.First_Name_M0040, p.Middle_Initial_M0040 ,p.SSN_M0064,
a.Certification_Start AS START, a.Certification_End AS END , a.Assessment_Reason_M0100 AS TYPE,
(SELECT Min(Visit_Date) FROM #Request.prefix_db_agency#.ScheduleItems
where Invoice_ID = i.Invoice_ID
and Deleted = 0 ORDER BY Visit_Date) as MIN,
(SELECT Max(Visit_Date) FROM #Request.prefix_db_agency#.ScheduleItems
where Invoice_ID = i.Invoice_ID
and Deleted = 0 ORDER BY Visit_Date) as MAX,
pr.PayName, pr.Payer_Id, pr.Contact, pr.Contact_Phone, pr.Contact_Phone_Ext,
pr.Output_Type AS PayerOutputType ,
(SELECT SUM(Amount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id) AS Paid ,
(SELECT SUM(WriteOffAmount) FROM #Request.prefix_db_agency#.Payments WHERE Invoice_Id = i.Invoice_Id) AS wOff ,
(select SUM(Total_Eclaim) from #Request.prefix_db_agency#.ScheduleItems where invoice_id = i.invoice_id
AND Total_EClaim > 0 AND Deleted <> 1 limit 1 ) as Total_Eclaim
FROM #Request.prefix_db_agency#.Invoices i
join #Request.prefix_db_agency#.patient p on p.patient_id = i.patient_id
join #Request.prefix_db_agency#.Assessment_New a on i.Assessment_Tracker_ID = a.Assessment_Tracker_ID
join #Request.prefix_db_agency#.Payer pr on i.PatientPayer_ID = pr.payer_id
WHERE
i.Agency_ID = #session.AgencyId#
AND i.Deleted <> 1
AND (i.claim_status ='cancelled' OR i.claim_status ='Paid')
AND (i.User_defined_Status = 'final paid/RAP cancelled' OR i.User_defined_Status = 'RAP cancelled/no final payment')
AND pr.PayerStatus = 'active'
AND i.InvoiceTotal > 0
Group by i.Invoice_Id
ORDER BY Last_Name_M0040, First_Name_M0040 ,MIN DESC