We’ve created a BAQ that generates a date difference between the invoice date and the customer payment date. We’re hoping to use this query to monitor how long it takes customers to pay specific invoices. The issue we’re having is that the query brings back all payments (progress payments), and we’re only interested in the difference between the invoice date and the final payment date.
Does anyone know how we could exclude all payments except the final payment on an invoice?
select
[Customer].[Name] as [Customer_Name],
[Customer].[CustID] as [Customer_CustID],
[InvcHead].[LegalNumber] as [InvcHead_LegalNumber],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[CashDtl].[TranDate] as [CashDtl_TranDate],
(convert(int, datediff(day, InvcHead.InvoiceDate, CashDtl.TranDate))) as [Calculated_DaystoPay]
from Erp.InvcHead as InvcHead
inner join Erp.CashDtl as CashDtl on
InvcHead.Company = CashDtl.Company
and InvcHead.InvoiceNum = CashDtl.InvoiceNum
and ( CashDtl.TranType = ‘PayInv’ )
inner join Erp.Customer as Customer on
CashDtl.Company = Customer.Company
and CashDtl.CustNum = Customer.CustNum
where (InvcHead.OpenInvoice = no)
Thanks,
Mike