Skip to Content

Question about Average time to pay, 5 most recent transactions

Oct 17, 2017 at 01:56 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? Any other resources generally on treating transactions separately? (I would also eventually like to be able to look at data points that fall outside a certain variance and chop them off.)

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

0 Answers