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


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
                               FROM            dbo.SOP30200
                               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.

sql query - last invoice date where invoice is greater than zero

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.