Dynamics GP AR Aging SQL Query Generator

USE [TWO];
GO

WITH open_items AS (
    SELECT
        o.CUSTNMBR,
        o.DUEDATE,
        CASE WHEN o.RMDTYPAL >= 7 THEN -o.CURTRXAM ELSE o.CURTRXAM END AS signed_amt,
        DATEDIFF(day, o.DUEDATE, '2026-09-30') AS days_past_due
    FROM dbo.RM20101 o
    WHERE o.VOIDSTTS = 0
      AND o.CURTRXAM <> 0
      AND o.DOCDATE <= '2026-09-30'
)
SELECT
    RTRIM(c.CUSTNMBR) AS customer_id,
    RTRIM(c.CUSTNAME) AS customer_name,
    SUM(CASE WHEN i.days_past_due <= 0 THEN i.signed_amt ELSE 0 END) AS current_not_due,
    SUM(CASE WHEN i.days_past_due BETWEEN 1 AND 30 THEN i.signed_amt ELSE 0 END) AS days_1_to_30,
    SUM(CASE WHEN i.days_past_due BETWEEN 30 + 1 AND 60 THEN i.signed_amt ELSE 0 END) AS days_30_to_60,
    SUM(CASE WHEN i.days_past_due BETWEEN 60 + 1 AND 90 THEN i.signed_amt ELSE 0 END) AS days_60_to_90,
    SUM(CASE WHEN i.days_past_due > 90 THEN i.signed_amt ELSE 0 END) AS over_90,
    SUM(i.signed_amt) AS total_open
FROM open_items i
JOIN dbo.RM00101 c ON c.CUSTNMBR = i.CUSTNMBR
WHERE 1 = 1
  AND c.CUSTCLAS = 'USA-ILMO-T1'
GROUP BY c.CUSTNMBR, c.CUSTNAME
HAVING SUM(i.signed_amt) <> 0
ORDER BY total_open DESC;