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

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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,370 pointsBadges:
    report
  • Sqldude
    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
    Source Reference: Title: Get inventory items with positive quantity-on-hand with latest corresponding invoice info
    report
  • Sqldude
    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
    ok, last try. here's the link Dear admin: think you should have a way to edit a recent post?
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following