Skip to Content
avatar image
Former Member

Question about Average time to pay, 5 most recent transactions

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

FROM ORCT T0

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.)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers