Simple Group By

[NaderC]

Hi, I have a database of products, orders and order details. I am trying to write a query that returns the most recent unit prices.
This means that I need to see each item that has been sold, and it's most recent price sold. This is what I wrote so far:

SELECT ItemNumber, Max(ClientOrderDate), UnitPrice
FROM (Orders
RIGHT JOIN OrderDetails ON Orders.OrderNumber = OrderDetails.OrderHeaderNumber)
WHERE OrderType LIKE '%STANDARD%' AND CustomerCode LIKE 'COST%'
GROUP BY ItemNumber, ClientOrderDate, UnitPrice
HAVING ClientOrderDate = MAX(ClientOrderDate)
ORDER BY ItemNumber

But I'm getting many dates, and not just the most recent ones. Does anyone know what I am doing wrong? I am using SQL Server 2000.

Thank you,

Nader

 

[r937]

which table is ClientOrderDate in?

why would OrderDetails have multiple prices for the same item?

how does the product table fit in, isn't that where the price would be?

r937.com | rudy.ca

 

[NaderC]

which table is ClientOrderDate in?

ItemNumber comes from the Products table.
ClientOrderDate comes from the Orders table.
UnitPrice comes from the OrderDetails table.
OrderType comes from the Orders table.
CustomerCode comes from the Orders table.

why would OrderDetails have multiple prices for the same item?

Items can be sold for different prices to each customer.

how does the product table fit in, isn't that where the price would be?

The products table is not needed for this query. It only contains infomration about each item.

Thanks,

Nader

 

[NaderC]

Just another note: ItemNumber can also come from the OrderDetails table.

 

[r937]

i'm lost, sorry

r937.com | rudy.ca

 

[PHV]

you wanted something like this ?

SELECT D.ItemNumber, L.LastDate, D.UnitPrice
FROM Orders O
INNER JOIN OrderDetails D ON O.OrderNumber = D.OrderHeaderNumber
INNER JOIN (
  SELECT Y.ItemNumber, Max(X.ClientOrderDate) LastDate
  FROM Orders X INNER JOIN OrderDetails Y ON X.OrderNumber = Y.OrderHeaderNumber
  WHERE X.OrderType LIKE '%STANDARD%' AND X.CustomerCode LIKE 'COST%'
  GROUP BY Y.ItemNumber
) L ON D.ItemNumber = L.ItemNumber
WHERE O.ClientOrderDate = L.LastDate
AND O.OrderType LIKE '%STANDARD%' AND O.CustomerCode LIKE 'COST%'
ORDER BY D.ItemNumber

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NaderC]

Yes PHV, I got something like this, thank you.

SELECT od.ItemNumber, o.InvoiceDate, od.UnitPrice
     FROM Orders o
INNER JOIN OrderDetails od
    ON o.OrderNumber = od.OrderHeaderNumber
INNER JOIN Customers c
    ON o.CustomerCode = c.CustomerCode
INNER JOIN
    (--==== Derived table "d" finds latest date each item was ordered
    SELECT dod.ItemNumber, MAX(do.InvoiceDate) AS LastOrderDate
        FROM Orders do
    INNER JOIN OrderDetails dod
        ON do.OrderNumber  = dod.OrderHeaderNumber
    INNER JOIN Customers dc
        ON dc.CustomerCode = do.CustomerCode
    WHERE do.OrderType LIKE '%STAN%'
        AND dc.CustomerName LIKE '%COSTC%'
    GROUP BY dod.ItemNumber
    ) d
ON d.ItemNumber = od.ItemNumber
    AND d.LastOrderDate = o.InvoiceDate
WHERE o.OrderType LIKE '%STAN%'
    AND c.CustomerName LIKE '%COSTC%'
GROUP BY od.ItemNumber, o.InvoiceDate, od.UnitPrice

Nader