SQL Query that only Returns the Newest Invoice date for an ITem
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
Software/Hardware used:
Software/Hardware used:
ASKED:
January 11, 2008 5:44 PM
UPDATED:
January 29, 2008 10:58 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
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
Source Reference:
Title: Get inventory items with positive quantity-on-hand with latest corresponding invoice info
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
ok, last try. here’s the link
Dear admin: think you should have a way to edit a recent post?