SQL Query that only Returns the Newest Invoice date for an ITem

Ken Foster

20 pts.

Tags:

Query

SQL

I am try to write a query that will give me a list of ITems that have a a quanity of greater than 0 on hand and only latest date and Item was invoiced. Here is what i have come up with so far SELECT IV00101.ITEMNMBR, IV00101.ITEMDESC, IV00101.CURRCOST, IV00102.QTYONHND,SOP30200.SOPNUMBE, SOP30200.INVODATE FROM IV00101 INNER JOIN IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR INNER JOIN SOP30300 ON IV00101.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON IV00102.LOCNCODE = SOP30200.LOCNCODE Where IV00102.QTYONHND > 0 SOP30200.INVODATE is the Invoice date field. I am stuck on this. any help would be great Thank you

Asked: January 11, 2008 5:44 PM Last updated: January 29, 2008 10:58 PM

Answer Wiki

Last updated: January 11, 2008 6:51 PM GMT

Denny Cherry68,410 pts.
History
Contributors
- Ordered by most recent
- Denny Cherry68,410 pts.
- FrankKulash1,240 pts.

Thanks. We'll let you know when a new response is added.

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

Denny Cherry Jan 11, 2008 6:50 PM GMT

Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.

68,410 pointsBadges:

report
Sqldude Jan 29, 2008 10:29 PM GMT

Here's some pointers: You need only the summary counts, hence you have to filter the RECORDTYPE of table IV00102, list only the master records, thus where iv00102.rcrdtype = 1 Also, you can not derive your output from one single SQL command, you have to utilize a temp table. To accomplish your intended output, use this script: -- Declare Variables declare @itemnmbr varchar(20), @itemdesc varchar(50), @qtyonhnd int -- Create a temporary table to hold rows create table #temp ( itemnmbr varchar(20), itemdesc varchar(50), qtyonhnd int, sopnumbe varchar(50), invodate datetime ) -- Get all qty-on-hand where record type = Summary inventory count declare c cursor for select v.itemnmbr, v.itemdesc, q.qtyonhnd ..CUT.. open c fetch next from c into @itemnmbr, @itemdesc, @qtyonhnd while (@@fetch_status <> -1) begin -- Collect them into the temporary table insert into #temp (itemnmbr, itemdesc, qtyonhnd, sopnumbe, invodate) fetch next from c into @itemnmbr, @itemdesc, @qtyonhnd ..CUT.. end deallocate c -- Output the generated rows select * from #temp -- Clean up drop table #temp Script Source: http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=65 Title: Get inventory items with positive quantity-on-hand with latest corresponding invoice info

40 pointsBadges:

report
Sqldude Jan 29, 2008 10:34 PM GMT

Source Reference: Title: Get inventory items with positive quantity-on-hand with latest corresponding invoice info

40 pointsBadges:

report
Sqldude Jan 29, 2008 10:35 PM GMT

for some reason, the link won't work.. so here it is. http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=65 http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=65

40 pointsBadges:

report
Sqldude Jan 29, 2008 10:58 PM GMT

ok, last try. here's the link Dear admin: think you should have a way to edit a recent post?

40 pointsBadges:

report