I am looking for either an SAP B1 report or query that would provide information on the average days each of our customers take to pay our invoices.
Give this a try
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))
I cannot seem to get this query to work - possibly does not work with SAP B1 Hana?
The query will work but you need to format it slightly differently to copy with the slight differences in the way that HANA expects to get the syntax of a SQL query.
I have just written a blog on this topic and posted a set of dcouments and tools to help in the process.
You can find it here: