Stymied help with query

1

I need to create a single record for unique item in the tblInventory_Hist table, selected to have the most recent DateRecvd.

I can get a single item as in:

    SELECT d.ItemId
        ,MAX(h.DateRecvd)
    FROM tblInventory_Hist h
    JOIN tblPurchaseOrderItems d ON h.PODetailID = d.ID
    JOIN tblItem i ON d.ItemID = i.ID
    GROUP BY d.ItemID
    ORDER BY d.ItemID

but I cannot figure out how to get back to tblInventory to join to tblPurchaseOrder... and tblItem. Then I can include columns like tblInventory_Hist.InvoiceNum, tblVendor.Name, tblItem.Number and tblItem.Description.

Can anyone help this sometime SQL guy?
CREATE TABLE [dbo].[tblItem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Number] nvarchar NULL,
[Description] nvarchar NULL,
...

CREATE TABLE [dbo].[tblInventory_Hist](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PODetailID] [int] NULL,
    [QTYRecvd] [int] NULL,
    [DateRecvd] [datetime] NULL,
    [InvoiceNum] [nvarchar](50) NULL,
    [InvoiceDate] [datetime] NULL,
    ...

CREATE TABLE [dbo].[tblPurchaseOrders](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [VendorID] [int] NULL,
    [OrderDate] [datetime] NULL,
    ...

CREATE TABLE [dbo].[tblPurchaseOrderItems](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderID] [int] NULL,
    [ItemID] [int] NULL,
    [QTY] [int] NULL,
    [Price] [money] NULL,
    ...

CREATE TABLE [dbo].[tblVendor](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
...
2

You need to add ItemId as foreign key in all the other tables that you want to display in your output.
And then join each table on the ItemId column,so that you get all the line details Item wise.

3

You should be able to join to tblPurchaseOrders using the OrderID from tblPurchaseOrderItems, like so:

FROM tblInventory_Hist h
JOIN tblPurchaseOrderItems d ON h.PODetailID = d.ID
JOIN tblPurchaseOrders o ON d.OrderID = o.ID
JOIN tblItem i ON d.ItemID = i.ID

You're already joined to tblItem, so I'm not sure what you mean by "and tblItem".

4

Thanks Scott, but how do I get a single unique row for each tblItem.ID from tblInventory_Hist, each row representing the most recently purchased item (tblInventory_Hist.DateRecvd)?

5

This perhaps?

; WITH X AS
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY d.ItemId
				ORDER BY d.ItemId, h.DateRecvd DESC
			),
		d.ItemId,
		h.DateRecvd
--		*** Any other columns from tblInventory_Hist or tblPurchaseOrderItems
--			which are required in the output
	FROM	tblInventory_Hist AS h
		JOIN tblPurchaseOrderItems AS d
			ON h.PODetailID = d.ID
--	WHERE	...
)

SELECT	
--	[T_RowNumber],
	X.ItemID,
	X.DateRecvd,
--	*** Any other columns from the CTE as required
--	*** Any columns from [tblItem] as required
FROM	X
	JOIN tblItem AS i
		ON i.ID = X.ItemID
WHERE	T_RowNumber = 1
ORDER BY X.ItemID