March 5, 2004 at 2:58 pm
Hi
I have a master table like this
StockId PurchaseNo PurchaseDate
StockId PurchaseNo PurchaseDate
------- ----------- -------------
1 1 2004/01/01
1 2 2004/01/02
1 3 2004/01/02
and a detail table like this
StockId PurchaseNo ItemId Quantity
------- ----------- ------ --------
1 1 100 10
1 1 200 20
1 2 100 5
1 2 300 30
1 3 300 15
I want to get the max Qunatity of the max PurchaseDate for each ItemId per StockId.
The result should be like this:
StockId ItemId Quantity
------- ------ --------
1 100 5
1 200 20
1 300 30
i've done it as follow. i first got the max PurchaseDate for every ItemId per StockId with the following query:
SELECT a.StockId, a.ItemId, MAX(b.PurchaseDate) PurchaseDate
FROM Detail a
INNER JOIN Master b
ON a.StockId = b.StockId AND a.PurchaseNo = b.PurchaseNo
WHERE a.Quantity <> 0
GROUP BY a.StockId, a.ItemId
then i joined another query with this derieved table like this:
SELECT a.StockId, a.ItemId, MAX(Quantity) Quantity
FROM Detail a
INNER JOIN Master b
ON a.StockId = b.StockId AND a.PurchaseNo = b.PurchaseNo
INNER JOIN
(
SELECT a.StockId, a.ItemId, MAX(b.PurchaseDate) PurchaseDate
FROM Detail a
INNER JOIN Master b
ON a.StockId = b.StockId AND a.PurchaseNo = b.PurchaseNo
WHERE a.Quantity <> 0
GROUP BY a.StockId, a.ItemId
) c
ON a.StockId = c.StockId AND
a.ItemId = c.ItemId AND
b.PurchaseDate = c.PurchaseDate
WHERE a.Quantity <> 0
GROUP BY a.StockId, a.ItemId
ORDER BY a.StockId, a.ItemId
but i think there should be a better solution for this.
any help will be appreciated
Thanks in advance
March 5, 2004 at 3:36 pm
Um without created those tables and trying it on mine. Wouldn't you:
SELECT dbo.detailtable.stockID, dbo.detailtable.purchasenumber, MAX(dbo.detailtable.quantity) AS Expr1
FROM dbo.detailtable INNER JOIN
dbo.mastertable ON dbo.detailtable.stockID = dbo.mastertable.stockID AND dbo.detailtable.purchasenumber = dbo.mastertable.PurchaseNum
GROUP BY dbo.detailtable.stockID, dbo.detailtable.purchasenumber
using the values you supplied above I got this:
stockID, purchaseNum, maxQuan
1 1 50
1 2 30
1 3 15
In your message you said: " want to get the max Qunatity of the max PurchaseDate for each ItemId per StockId. "
which I read as max qty of each item each day. But your example didn't include the date?
SELECT dbo.mastertable.purchasedate, dbo.mastertable.PurchaseNum, dbo.mastertable.stockID, MAX(dbo.detailtable.quantity) AS Expr1
FROM dbo.detailtable INNER JOIN
dbo.mastertable ON dbo.detailtable.stockID = dbo.mastertable.stockID AND dbo.detailtable.purchasenumber = dbo.mastertable.PurchaseNum
GROUP BY dbo.mastertable.purchasedate, dbo.mastertable.PurchaseNum, dbo.mastertable.stockID
with Dates I get:
date,purchaseNum,stockID, maxQuan
1/1/2004 1 1 50
1/2/2004 2 1 30
1/2/2004 3 1 15
I think you should be able to line up your two tables in query desugner and choose which fields you want to join on, and the order of your groups and you should get what your after.
I'll read your message again after picking up my kids from school.
dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 5, 2004 at 4:28 pm
your query will return MAX(Quantity) for each PurchaseNo. but i want to get something else. Consider the following de-normalized view of data:
StockId P_No P_Date ItemId Quantity
1 1 1/1/2004 100 50
1 2 1/2/2004 100 150
1 3 1/2/2004 100 200
1 4 1/3/2004 100 10
1 5 1/3/2004 100 30
1 6 1/3/2004 100 20
I want to get
1 100 30
because for ItemId = 100, Max Date is 1/3/2004 and in that date Max Quantity is 30.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply