Hi,
Is the query you posted getting the results you want, except that it’s including all invodates, and not just the latest one? Then add this to the WHERE-clause:
<pre>
AND invodate =
( — Begin sub-query to get latest invoice date for item
SELECT MAX (invodate)
FROM sop30200 AS s
WHERE s.itemnmbr = sop30200.itemnmbr
) — End sub-query to get latest invoice date for item
</pre>
The code above works in SQLServer; you may have to modify it a little for other products.
If there is a tie for the latest invodate for a given item, then all rows with that invodate will be included. If all rows for a given item have a NULL invodate, that item will not be included.
Discuss This Question: 5  Replies