Link to home
Start Free TrialLog in
Avatar of rsts_support
rsts_support

asked on

Trouble with SQL query using MAX function

I am having trouble trying to get a query to work that involves multiple tables and the use of the MAX function. I’m pretty sure a subquery is needed to get it to work but am having trouble figuring out how to do this.

I basically have three tables—Item, InvItem, and Invoice. The “Item” table keeps track of items that are for sale, the “Invoice” is an actual sales transaction, and “InvItem” are the individual items that are sold at each Invoice.

What I am trying to do is to return the last date that an item was last sold on and the number of the items sold during that last invoice for the particular item. I have no trouble getting the last date an item was sold on, but can’t figure out how to get the quantity of the item sold on that date. Here is the SQL to get the last date an item was sold on:

SELECT Max(Invoice.InvoiceDate) AS MaxOfInvoiceDate, InvItem.ItemID
FROM InvItem INNER JOIN Invoice ON InvItem.InvoiceID = Invoice.InvoiceID
GROUP BY InvItem.ItemID;

This gives me every "ItemID" that was sold on an invoice and the date of that invoice.  However I can’t add “InvItem.Qty” to the SELECT statement since it isn’t going to be unique. I would think that I would need to do a subquery here but I’m a little unsure how to do it. I’ve done subqueries in the WHERE clause before on a single field but I would think I would need to use two fields here but I'm not even sure that would work either.

Does anyone have any suggestions? Any help is much appreciated. Thanks!
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

a sample db with the relevant table will help..
>This gives me every "ItemID" that was sold on an invoice and the date of that invoice.
Actually MAX states that it only gives the most recent invoice date, not all invoice dates.

>However I can’t add “InvItem.Qty” to the SELECT statement since it isn’t going to be unique.
So tell us what you're looking for, most recent invoice date and count/sum of Qty? Specifically, eyeball SQL Server GROUP BY Solutions, part 5. Aggregate AND values from a single row that make up the aggregate: Subquery, and let us know if that's what you're after.
Avatar of rsts_support
rsts_support

ASKER

Jim Horn, MAX gives me the most recent invoice date PER item (e.g. screws, bolts, etc)  which is what I want (the "Group By ItemID" does this). In other words if item X was sold on two separate invoices (hence two separate dates) which is the more recent of the two dates?

What I am looking for is the not only the last date that a particular item was sold but also the quantity that was sold of that item on that date. I don't need to use an aggregate function on "Qty" because I just need a one record value for Quantity. So if Item A was last sold on  6/7/2015 on Invoice B, and there were 5 units of it sold, then the "Qty" would be 5. The user is seeking this information so they can answer the question "When was the last time that item X was sold and how many of it were sold?. If it was a LONG time ago that item X was last sold, and there weren't very many units of it sold during that transaction, then maybe I shouldn't be CARRYING that item any longer..."

I created an Access database example that parrots the actual vendor database just so I can figure out how to solve the issue. I've attached the Access database to this message. In this database take a look at the query "LastDatebyItem". This selects the last date per item of each item type sold. Along with this I want to get the actual "Qty" sold from the "InvItem" table as well. This is what I can't figure out how to do.
TestDB.accdb
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
here is your sample db, see query2
TestDB.accdb
You nailed it Rey! Thank you so much!

I kept thinking in terms of using the WHERE clause but I didn't think about doing a JOIN on both of those fields.