Help with Last Sold Query

[UltraSmooth]

I need to write a query selects all items from an inventory table 'MenuItem' that have sold since a user specified date. The sales table 'Custinv' has a record for every item sold by date and location (quite a large amount of records).

I'm not really sure how to proceed, should I be joining with the sales table? Won't this procedure have to do a full table scan to find the latest date the item was sold?

I was thinking maybe I had to query the sales table to bring back a list of items and the last date they were sold and then join to the inventory table to get the item detail information.

Needless to say, I'm a little confused. Any help would be appreciated.

 

[RiverGuy]

SELECT a.* FROM MenuItem a

INNER JOIN

(SELECT DISTINCT ItemID
FROM CustInv
WHERE SoldDate >= @SomeDate
) b

ON a.ItemID = b.ItemID

 

[UltraSmooth]

Here's what I've come up with so far, and I just noticed it's very similar to Riverguy's response,

SELECT MenuItem.*
	, sales.*
FROM MenuItem
JOIN (
SELECT DISTINCT custitem.PLU
	, custitem.LocationNo
	, MAX(Custinv.InvoiceDate) AS LastSold
FROM Custinv JOIN custitem 
  ON Custinv.InvoiceNo=custitem.InvoiceNo
 AND Custinv.LocationNo=custitem.LocationNo
WHERE Custinv.InvoiceDate>='06-01-2009'
GROUP BY custitem.PLU
	, custitem.LocationNo
) AS sales
ON MenuItem.PLU=sales.PLU
AND MenuItem.LocationNo=sales.LocationNo

Does my query make sense?

 

[markros]

SQL Server 2005 and up

;with cte as (select M.*, S.SoldDate, S.Price, row_number() over (partition by M.Item order by S.SoldDate DESC) as row
from MainItems M LEFT JOIN CustInv S on M.ItemID = S.ItemID)

select * from cte where row = 1

Take a look at these tow links
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

PluralSight Learning Library

 

[RiverGuy]

Does my query make sense?

It looks good to me.

 

[markros]

You don't need to use DISTINCT if you're doing GROUP BY.

PluralSight Learning Library

 

[djj55]

One more point, UltraSmooth asked "Won't this procedure have to do a full table scan to find the latest date the item was sold?"

An index can help performance.


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!