sql server mseq: Hi
I want to select records where the amount of stock on hand is greater than
the amount allocated and then to value it with the last cost on the last
record in another table

EG
Cost Table
Date Cost
10/11/2003 1.00
14/10/2003 3.00
15/11/2003 2.00

StockcodeMaster Table
StockCode Warehouse ProductClass
PartA R XB

Stock Table
Stockcode QtyOnHand QtyAllocatedWip
PartA 100 50

Resultset Required:
StockCode QtyOnHand QtyAllocatedWip Surplus
LastCost LastReceipt TotalValue
PartA 100 50
50 2.00 15/11/2003 100
(2 * 50)

I don't know how to get the last cost into the query as it says I can't have
Cost in the select statement as not part of aggregate etc

What am I doing wrong

Here is what I have so far

SELECT W.StockCode, W.QtyOnHand, W.QtyAllocatedWip,
W.QtyOnHand-W.QtyAllocatedWip as Surplus, Max(B.LastReceiptDate) as
LastReceipt,
(W.QtyOnHand - W.QtyAllocatedWip) * B.Cost as TotalValue
FROM StockCodeMaster M INNER JOIN Stock W ON M.StockCode = W.StockCode INNER
JOIN Cost B ON W.StockCode = B.StockCode
WHERE W.Warehouse = 'R' and M.ProductClass Like 'X%'
GROUP BY W.StockCode, W.QtyOnHand, W.QtyAllocatedWip, B.UnitCost1
HAVING (W.QtyOnHand-W.QtyAllocatedWip) > 0

Thanks but . .. .no that's my problem their are multiple records in the
cost table and I want to use the cost that is associated with the
max(lastReceiptDate). If I use max(B.Cost) then this is not the same
because it gives me the maximum Cost rather than the cost associated with
Max(LastReceiptDate)

Should there be a subquery in there somewhere?

Thanks for your help

[quoted text, click to view]

Newbie,

This is obvious because B.Cost is not part of either aggregate function or not part of group by.To
make your query error free and have desired result set you will have to include it either in group
by clause or make it a part of aggregate function like min/max.

If there is going to be only one row in COST table for a particular stock code you can have max or
min function to return only one value for COST column.

Ex:

SELECT W.StockCode, W.QtyOnHand, W.QtyAllocatedWip,
W.QtyOnHand-W.QtyAllocatedWip as Surplus, Max(B.LastReceiptDate) as
LastReceipt,
(W.QtyOnHand - W.QtyAllocatedWip) * max(B.Cost) as TotalValue
FROM StockCodeMaster M INNER JOIN Stock W ON M.StockCode = W.StockCode INNER
JOIN Cost B ON W.StockCode = B.StockCode
WHERE W.Warehouse = 'R' and M.ProductClass Like 'X%'
GROUP BY W.StockCode, W.QtyOnHand, W.QtyAllocatedWip, B.UnitCost1
HAVING (W.QtyOnHand-W.QtyAllocatedWip) > 0


--
- Vishal

Newbie,
yes, probably using subquery will solve your problem. But make sure there is only one row that has
been returned by the subquery or else you will get another error something as "Subquery returned
more than 1 value".
Try:

SELECT W.StockCode, W.QtyOnHand, W.QtyAllocatedWip,
W.QtyOnHand-W.QtyAllocatedWip as Surplus, Max(B.LastReceiptDate) as
LastReceipt,
(W.QtyOnHand - W.QtyAllocatedWip) *
(select Cost from cost where stockcode = w.stockcode and lastreceiptdate = Max(B.LastReceiptDate))
as TotalValue
FROM StockCodeMaster M INNER JOIN Stock W ON M.StockCode = W.StockCode INNER
JOIN Cost B ON W.StockCode = B.StockCode
WHERE W.Warehouse = 'R' and M.ProductClass Like 'X%'
GROUP BY W.StockCode, W.QtyOnHand, W.QtyAllocatedWip, B.UnitCost1
HAVING (W.QtyOnHand-W.QtyAllocatedWip) > 0


--
- Vishal