How can I make this query return not just the max invoice date, but latest invoice date where the invoice is greater than zero? Ignore zero dollar invoices. The field is Docamnt for invoice amount.
SELECT DISTINCT t1.CUSTNMBR, t2.max_GLPOSTDT
FROM dbo.SOP30200 AS t1 INNER JOIN
(SELECT CUSTNMBR, MAX(GLPOSTDT) AS max_GLPOSTDT
GROUP BY CUSTNMBR) AS t2 ON t1.CUSTNMBR = t2.CUSTNMBR
So I want to find the last invoice date for each customer where the invoice is greater than zero.