The payment behaviour module of the customer app from the series Building a customer app in Uniface 10 has an operation "days to payment": per invoice month the number of paid invoices, the average days to payment, how many were paid late and by how many days on average.
Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, October 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).
The finding
The test data has three paid invoices in one month; one of them was paid 11 days after the due date. The form showed:
2026-09: 3 invoice(s), 11,7 day(s) on average, 1 late by 3,7 day(s) on average "1 late by 3,7 days" is wrong: the only late invoice was 11 days late. The automated test did not catch it, because its expected values were computed with the same SQL. The click test did, because the sentence on the screen did not fit the data.
Before
REPLACE(printf('%.1f', AVG(CASE WHEN d.DUE_DATE IS NOT NULL AND p.LASTPAY > d.DUE_DATE THEN julianday(p.LASTPAY) - julianday(d.DUE_DATE) ELSE 0 END)), '.', ',') AS AVG_LATE For the two punctual invoices the CASE returns 0, so AVG divides 11 by 3.
After
REPLACE(printf('%.1f', COALESCE(AVG(CASE WHEN d.DUE_DATE IS NOT NULL AND p.LASTPAY > d.DUE_DATE THEN julianday(p.LASTPAY) - julianday(d.DUE_DATE) END), 0)), '.', ',') AS AVG_LATE Without ELSE the CASE returns NULL for punctual invoices, and AVG ignores NULL. COALESCE(..., 0) keeps the value at 0,0 for a month without any late invoice; otherwise printf would get NULL.
The form text was changed as well, so the two numbers cannot be mixed up:
2026-09: 3 invoice(s), 11,7 day(s) to payment on average, 1 paid late, 11,0 day(s) late on average The whole query
SELECT strftime('%Y-%m', d.DOC_DATE) AS MON, COUNT(*) AS N, REPLACE(printf('%.1f', AVG(julianday(p.LASTPAY) - julianday(d.DOC_DATE))), '.', ',') AS AVG_DAYS, SUM(CASE WHEN d.DUE_DATE IS NOT NULL AND p.LASTPAY > d.DUE_DATE THEN 1 ELSE 0 END) AS LATE_N, REPLACE(printf('%.1f', COALESCE(AVG(CASE WHEN d.DUE_DATE IS NOT NULL AND p.LASTPAY > d.DUE_DATE THEN julianday(p.LASTPAY) - julianday(d.DUE_DATE) END), 0)), '.', ',') AS AVG_LATE FROM SALES_DOC d JOIN %%(vPay) p ON p.DOC_ID = d.DOC_ID WHERE d.DOC_TYPE = 'INVOICE' AND d.STATUS = 'PAID' AND d.DOC_DATE >= date('now', 'localtime', 'start of month', '-%%(pMonths) months', '+1 months') GROUP BY 1 ORDER BY 1 %%(vPay) is a subquery with the last payment per invoice that was not reversed; the date filter gives the last months including the current one.
Notes
- A test was added that checks this value for the late invoice (11,0). Expected values that come from the same query only prove that the service runs, not that the query is right; at least one value per operation should be worked out by hand.
- The same pattern appears in other averages:
AVG(CASE WHEN x THEN value ELSE 0 END)is the average over all rows,AVG(CASE WHEN x THEN value END)the average over the rows where x is true. Both can be wanted - the label has to say which one it is.

