Skip to Content
0
Former Member
Dec 05, 2011 at 10:40 AM

User query - Average payment days

69 Views

Hi,

I made a query to calculate the average number of days of payment for a customer using the journal transactions:

+SELECT AVG((DateDiff(DD, JDT1.DueDate, JDT1.MthDate)))

FROM OJDT INNER JOIN JDT1 ON OJDT.TransId = JDT1.TransId LEFT JOIN ORCT ON OJDT.TransId = ORCT.TransId

WHERE (JDT1.TransType = 13 OR JDT1.TransType = 203) AND JDT1.shortname='C0001' AND (JDT1.balduedeb-JDT1.balduecred) = 0 AND (ORCT.BoeSum = 0 OR ORCT.BoeSum IS NULL)+

But now I have a problem with payments made in the presence of Bill of Exchange because, in this case, the reconciliation date coincides with that of incoming payment and not with the expiration date of the Bill of Exchange.

Do you have any suggestions to improve this query???

Thanks!