Skip to Content

Average Days to Pay using most recent transactions

Oct 16, 2017 at 06:19 PM


avatar image
Former Member

Hi, I have a lovely average days to pay query written by Gordon Du:

SELECT T0.CardName, AVG(DateDiff(dd,T2.DocDate,T0.DocDate)) 'Average Days', T4.PymntGroup


JOIN RCT2 T1 ON T1.DocNum = T0.DocNum

JOIN OINV T2 ON T2.DocEntry = T1.DocEntry

JOIN OCRD T3 ON T3.CardCode=T0.CardCode

JOIN OCTG T4 ON T4.GroupNum=T3.GroupNum

WHERE T0.CardName LIKE '[%0\]%'

GROUP BY T0.CardName, T4.PymntGroup

ORDER BY AVG(DateDiff(dd,T2.DocDate,T0.DocDate))

My payment dates are booked using the TrsfrDate field in the money table (OCRT), rather than the posting date, so I alter my formula to SELECT T0.CardName, AVG(DateDiff(dd,T2.DocDate,T0.TrsfrDate)) 'Average Days', T4.PymntGroup.

Beyond that, I want to draw from only the 5 most recent transactions. How can I parse those out of my data?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Oct 16, 2017 at 07:09 PM

Hi John, is this related to Predictive? I think you should retag this. Kind regards Antoine

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Antoine,

I guess not really.. receivables projections...? Not sure what category this would fall under.


Sorry but I don't know.


Tammy Powlas suggested that this should be tagged with Business One. Would that correspond to your need?