SELECT UnitCost on MAX(OrderDate) from another table

We have an ERP system with a Purchase Order header table (POHeader) and a Purchase Order lines table (PODetail). There are hundreds of thousands of POLines where many, many parts have been repeatedly purchased over several years. I need to find the UnitCost of each part purchased from the last time it was purchased (max(OrderDate) of the PO).

I have been playing with SQL MAX(DATE) Order Number for which I have nearly the same identical requirements. If we look at the solution code in that thread:

SELECT t.ClientId, t.MaxDate, rderNumber
FROM (SELECT ClientId, MAX(Date) as MaxDate
FROM dbo.tblOrders
GROUP BY ClientId) t
INNER JOIN dbo.tblOrders o
ON t.ClientId = o.ClientId
AND t.MaxDate = o.Date


My needs are identical where my Part Number is his ClientId and my UnitCost is his OrderNumber. A quick substitution would give me something like this:

SELECT t.PartNum, t.MaxDate, o.UnitCost
FROM (SELECT PartNum, MAX(OrderDate) as MaxDate
FROM POdetail
GROUP BY PartNum) t
INNER JOIN POdetail o
ON t.PartNum = o.PartNum
AND t.MaxDate = o.Date


The rub is that Purchase Order dates are stored at the header level and there are no dates in the detail data. I have to LEFT JOIN in the max(OrderDate) from the POHeader table and that's where I'm having trouble.

I've tried to compound in the OrderDate from the POHeader table but my code is wrong. Here's where I stopped. Can someone help my out with this statement please?:

SELECT d.PartNum, h.MaxDate, d.UnitCost
FROM
PODetail d
Left Join POHeader h ON
(SELECT PONum, MAX(OrderDate) as MaxDate
FROM POHeader
GROUP BY PONum) t
INNER JOIN POHeader o
ON t.PONum = o.PONum
AND t.MaxDate = rderDate


Here's some fool-around data...

create table POHeader (POnum int, OrderDate date);
insert into POHeader (PONum, OrderDate) values (12508, '05/13/2010');
insert into POHeader (PONum, OrderDate) values (12690, '06/04/2010');
insert into POHeader (PONum, OrderDate) values (12847, '09/08/2010');
create table PODetail (PONum int, PartNum varchar(15), Descript varchar(60), UnitCost numeric(10,2));
insert into PODetail (PONum, PartNum, Descript, UnitCost) values (12508, 'X37-BC055', 'PIPE, Titanium (MIL-T-9046), 3"OD x 8FT x .0125"', 6647.18);
insert into PODetail (PONum, PartNum, Descript, UnitCost) values (12690, 'X37-BC055', 'PIPE, Titanium (MIL-T-9046), 3"OD x 8FT x .0125"', 6651.90);
insert into PODetail (PONum, PartNum, Descript, UnitCost) values (12847, 'X37-BC055', 'PIPE, Titanium (MIL-T-9046), 3"OD x 8FT x .0125"', 6713.65);
insert into PODetail (PONum, PartNum, Descript, UnitCost) values (12690, 'N76-18824-ABN', 'COLLAR, Titanium (AMS 4902), 3"ID Flange', 564.13);
insert into PODetail (PONum, PartNum, Descript, UnitCost) values (12847, 'N76-18824-ABN', 'COLLAR, Titanium (AMS 4902), 3"ID Flange', 571.26);


You can see in the history data that I bought the 8-foot pipe three times and the collar twice. I'm trying to get to this result - the very last time I bought each part:

PartNum Max Date Last Price
------------- ---------- ----------
X37-BC055 9/08/2010 6713.65
N76-18824-ABN 6/04/2010 571.26


Thanks for your help, JM

Continue reading...