How to extract last transaction of Customer in AX 2009
SELECT MAX(CUSTTRANS.TRANSDATE) AS TRANSDATE, CUSTTRANS.ACCOUNTNUM, CUSTTABLE.NAME, (CASE WHEN CUSTTABLE.BLOCKED=0 THEN 'ACTIVE' WHEN CUSTTABLE.BLOCKED=1 THEN 'INVOICE' WHEN CUSTTABLE.BLOCKED=2 THEN 'BLOCK' ELSE '' END) AS STATUS FROM CUSTTRANS INNER JOIN CUSTTABLE ON CUSTTRANS.ACCOUNTNUM = CUSTTABLE.ACCOUNTNUM WHERE (CUSTTRANS.DATAAREAID = 'MRP') GROUP BY CUSTTRANS.ACCOUNTNUM, CUSTTABLE.NAME, CUSTTABLE.BLOCKED