Aging Report
Notes: • Adjust OINV to OPCH if this is for a payable aging report (suppliers instead of customers). • Replace GETDATE() with the appropriate function if your SQL environment uses a different function for the current date.
5/8/20241 min read
SELECT
T0.CardCode AS 'Customer Code',
T0.CardName AS 'Customer Name',
SUM(CASE WHEN DATEDIFF(DAY, T1.DocDueDate, GETDATE()) BETWEEN 0 AND 30 THEN T1.DocTotal ELSE 0 END) AS '0-30 Days',
SUM(CASE WHEN DATEDIFF(DAY, T1.DocDueDate, GETDATE()) BETWEEN 31 AND 60 THEN T1.DocTotal ELSE 0 END) AS '31-60 Days',
SUM(CASE WHEN DATEDIFF(DAY, T1.DocDueDate, GETDATE()) BETWEEN 61 AND 90 THEN T1.DocTotal ELSE 0 END) AS '61-90 Days',
SUM(CASE WHEN DATEDIFF(DAY, T1.DocDueDate, GETDATE()) BETWEEN 91 AND 120 THEN T1.DocTotal ELSE 0 END) AS '91-120 Days',
SUM(CASE WHEN DATEDIFF(DAY, T1.DocDueDate, GETDATE()) > 120 THEN T1.DocTotal ELSE 0 END) AS '120+ Days'
FROM
OCRD T0
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE
T1.DocDueDate <= GETDATE()
GROUP BY
T0.CardCode, T0.CardName
ORDER BY
T0.CardCode;
Solutions
Empowering businesses with ERP expertise and training.
Support
Growth
erpfactory@outlook.com
© 2024. All rights reserved.