group by select query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

group by select query problem

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2009-04-18 : 23:47:48

I have a product table, purchase order header table, purchase order detail table, and vendor table.

I need to create a list of the most recent date each product has been purchased, the purchase date and the cost for a particular vendor.

So if there is a purchase order that includes apples for jan 1 2009 and mar 1 2009, the apples would be listed with the date mar 1 and the cost at listed in the purchase order detail table for that item. If 2 purchase orders contain beef and the most recent is 1/2/2009, then that date and cost at that time appears, etc.

appcode apples 3/1/2009 20.50
bcode beef 1/2/2009 14.56
ccode chicken 7/2/2008 3.45

Items are sold by weight or unit, so the total cost is calulated differently depending upon how sold. The query I have written does not return only the most recent purchase date for a product, but all dates of purchase for those products that were purchased from a particular vendor

It worked until I brought in the cost fields then the problem above occurred. Here it is and maybe you can help me work this out. Thanks much, smh.

SELECT tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName,
MAX(tblProductPurchaseHeader.PurchaseDate),

CASE
when tblProduct.SellBy = 'W' then round(tblProductPurchaseDetail.TotalWeight * tblProductPurchaseDetail.CostPerPound,2)
when tblProduct.SellBy = 'U' then round(tblProductPurchaseDetail.CasesOrUnits * tblProductPurchaseDetail.CostPerUnit,2)
END as TotalCost

FROM tblProduct INNER JOIN
tblProductPurchaseDetail ON tblProduct.ProductID = tblProductPurchaseDetail.ProductID INNER JOIN
tblProductPurchaseHeader ON tblProductPurchaseDetail.ProductPurchaseID = tblProductPurchaseHeader.ProductPurchaseID

WHERE (tblProductPurchaseHeader.VendorID = 3)

GROUP BY tblProduct.ProductCode, tblProduct.ProductName, tblProductPurchaseDetail.ProductID, TotalCost,tblProduct.SellBy,tblProductPurchaseDetail.TotalWeight,
tblProductPurchaseDetail.CostPerPound,tblProductPurchaseDetail.CasesOrUnits ,tblProductPurchaseDetail.CostPerUnit

ORDER BY tblProduct.ProductCode

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-19 : 01:17:08

[code]SELECT tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName,
MAX(tblProductPurchaseHeader.PurchaseDate),
MAX(CASE
when tblProduct.SellBy = 'W' then round(tblProductPurchaseDetail.TotalWeight * tblProductPurchaseDetail.CostPerPound,2)
when tblProduct.SellBy = 'U' then round(tblProductPurchaseDetail.CasesOrUnits * tblProductPurchaseDetail.CostPerUnit,2)
END) as TotalCost
FROM tblProduct INNER JOIN
tblProductPurchaseDetail ON tblProduct.ProductID = tblProductPurchaseDetail.ProductID INNER JOIN
tblProductPurchaseHeader ON tblProductPurchaseDetail.ProductPurchaseID = tblProductPurchaseHeader.ProductPurchaseID
WHERE (tblProductPurchaseHeader.VendorID = 3)
GROUP BY tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName
ORDER BY tblProduct.ProductCode[/code]

smh
Yak Posting Veteran

94 Posts

Posted - 2009-04-19 : 11:44:18

Thanks so much. That works. It did not occur to me to do an aggregate sum on the calculation although adding that to the query caused the problem.

smh

smh
Yak Posting Veteran

94 Posts

Posted - 2009-04-21 : 15:30:14

Unfortunately, the specs were changed on this and I have a problem. Originally I had to show the totalcost field - see post below.

now, I just need to show two numerical fields (costperpount, costperunit) that are in the row with the most recent purchase date and not the totalcost. When I do the following:

SELECT tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName,tblProductPurchaseDetail.CasesOrUnits, tblProductPurchaseDetail.costperweight, tblProductPurchaseDetail.CostPerUnit,
MAX(tblProductPurchaseHeader.PurchaseDate),
FROM tblProduct INNER JOIN
tblProductPurchaseDetail ON tblProduct.ProductID = tblProductPurchaseDetail.ProductID INNER JOIN
tblProductPurchaseHeader ON tblProductPurchaseDetail.ProductPurchaseID = tblProductPurchaseHeader.ProductPurchaseID
WHERE (tblProductPurchaseHeader.VendorID = 3)
GROUP BY tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName, tblProductPurchaseDetail.CasesOrUnits , tblProductPurchaseDetail.costperweight, tblProductPurchaseDetail.CostPerUnit
ORDER BY tblProduct.ProductCode

of course I get no grouping but all the productpurchase details for each product.

When I do the following:

SELECT tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName,tblProductPurchaseDetail.CasesOrUnits ,
max(tblProductPurchaseDetail.CostperPound) as costperpound,
Max(tblProductPurchaseDetail.CostperUnit) as CostperUnit,
MAX(tblProductPurchaseHeader.PurchaseDate),
FROM tblProduct INNER JOIN
tblProductPurchaseDetail ON tblProduct.ProductID = tblProductPurchaseDetail.ProductID INNER JOIN
tblProductPurchaseHeader ON tblProductPurchaseDetail.ProductPurchaseID = tblProductPurchaseHeader.ProductPurchaseID
WHERE (tblProductPurchaseHeader.VendorID = 3)
GROUP BY tblProductPurchaseDetail.ProductID, tblProduct.ProductCode, tblProduct.ProductName, tblProductPurchaseDetail.CasesOrUnits
ORDER BY tblProduct.ProductCode

I get correctly only the record with the most recent purchasedate for each product but I don't get the costperunit and costperweight for that record, I get the record that has the largest cost per weight for that product (costperunit is always zero so that doesn't matter here).

so if this is the data

date costperwt costperunit
appcode apples 1/1/2009 20.50 0
appcode apples 2/1/2009 2.50 0
appcode apples 3/1/2009 3.50 0

The result becomes
appcode apples 3/1/2009 20.50 0

instead of

appcode apples 3/1/2009 3.50 0

I tried adding the tblProductPurchaseDetail.ProductPurchaseDetailID which is the primary key of that table, but that did not work.

Any help greatly appreciated.

smh
Yak Posting Veteran

94 Posts

Posted - 2009-04-21 : 19:32:53

I think this is the solution:

select ProductCode,
ProductCodeNumeric,
ProductName,
SellByWeightOrUnit,
OrderByWeightOrUnit,
PurchaseDate,
CostPerPound,
CostPerUnit
from (
select tblProduct.ProductCode,tblProduct.ProductCodeNumeric, tblProduct.ProductName,
tblProduct.SellByWeightOrUnit, tblProduct.OrderByWeightOrUnit, tblProductPurchaseHeader.PurchaseDate, tblProductPurchaseDetail.CostPerPound, tblProductPurchaseDetail.CostPerUnit,
ROW_NUMBER() OVER (PARTITION BY tblProductpurchasedetail.productID ORDER BY tblProductPurchaseheader.PurchaseDate DESC) AS RecID
FROM tblProduct INNER JOIN
tblProductPurchaseDetail ON tblProduct.ProductID = tblProductPurchaseDetail.ProductID INNER JOIN
tblProductPurchaseHeader ON tblProductPurchaseDetail.ProductPurchaseID = tblProductPurchaseHeader.ProductPurchaseID
WHERE tblProductPurchaseHeader.VendorID =3 ) as d
where RecID = 1 order by productcodenumeric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources