CODEWITHSUNDEEP
sqlsql-server-2008group-by
Richard
Richard

Reputation: 105

Finding Customers Last Price Paid

I'm trying to find a way to get customers last price paid the code we have at the moment is: 

SELECT Product.ProductCode,
       COUNT(Product.ProductCode) AS [Qty Baught],
       Product.Description,
       Customer.CustomerCode,
       Customer.Name,
       MAX(OrderHeader.DateTimeCreated) AS [Date],
       OrderLine.UnitSellPriceInCurrency AS Sell
FROM Customer
  INNER JOIN OrderHeader ON Customer.CustomerID = OrderHeader.CustomerID
  INNER JOIN OrderLine ON OrderHeader.OrderID = OrderLine.OrderID
  INNER JOIN Product ON OrderLine.ProductID = Product.ProductID
GROUP BY Product.Description,
         Product.ProductCode,
         Customer.CustomerCode,
         Customer.Name,
         OrderLine.UnitSellPriceInCurrency
HAVING (Product.ProductCode = 'bcem002')
   AND (Customer.CustomerCode = '1000')
ORDER BY MAX(OrderHeader.DateTimeCreated) DESC

This code shows every time the price changed but I only want to see the last price, But the DateCreated and the price paid (UnitSellPriceInCurrency) are on different tables.

Is there a way to Group (UnitSellPriceInCurrency) by (DateCreated) or an alternative way of doing it.

I'm fairly new at this so if there's an obvious way of doing this sorry.

Edit: What i'm getting at the moment with new code, Most of the prices i'm getting up are unrelated to the products

enter image description here

What I want is to get just the last price paid showing but in a way I can change the customer and the product that I'm searching for.

Upvotes: 1

Views: 477

Answers (2)

Mackan
Mackan

Reputation: 6271

One option might be to use a sub-select utilizing TOP to specify that you only want to retrieve one record, and make sure that it is the "latest" by using the ORDER BY:

SELECT Product.ProductCode,
       COUNT(Product.ProductCode) AS [Qty Baught],
       Product.Description,
       Customer.CustomerCode,
       Customer.Name,
       MAX(OrderHeader.DateTimeCreated) AS [Date],
         (SELECT TOP 1 O.UnitSellPriceInCurrency 
          FROM OrderLine O 
          INNER JOIN OrderHeader OH ON O.OrderID = OH.OrderID 
          WHERE OH.CustomerID = Customer.CustomerID AND O.ProductID = Product.ProductID
          ORDER BY OH.DateTimeCreated DESC) AS LatestPrice
FROM Customer
  INNER JOIN OrderHeader ON Customer.CustomerID = OrderHeader.CustomerID
  INNER JOIN OrderLine ON OrderHeader.OrderID = OrderLine.OrderID
  INNER JOIN Product ON OrderLine.ProductID = Product.ProductID
WHERE  (Customer.CustomerCode = '1000') AND (Product.ProductCode = 'bcem002')
GROUP BY Product.Description,
         Product.ProductCode,
         Product.ProductID,
         Customer.CustomerCode,
         Customer.Name,
         Customer.CustomerID
ORDER BY [Date] DESC

In this example LatestPrice will contain the last inserted UnitSellPriceInCurrency for each Customer and product. I'm not sure if the query makes sense with your data (why get only the last price) but it's how I interpreted your request. The query will still return all OrderHeaders though.

Upvotes: 1

Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

Can you please check of this works alright. It would be better if you posted a snapshot of the current result and the expected result you need.

            Select 
        *
        FROM
        (
        SELECT Product.ProductCode,
               COUNT(Product.ProductCode) AS [Qty Baught],
               Product.Description,
               Customer.CustomerCode,
               Customer.Name,
               OrderHeader.DateTimeCreated AS [Date],
               OrderLine.UnitSellPriceInCurrency AS Sell
               RANK() OVER (PARTITION BY Customer.CustomerCode,Customer.Name, OrderLine.UnitSellPriceInCurrency ORDER BY OrderHeader.DateTimeCreated) rnk
        FROM Customer
          INNER JOIN OrderHeader ON Customer.CustomerID = OrderHeader.CustomerID
          INNER JOIN OrderLine ON OrderHeader.OrderID = OrderLine.OrderID
          INNER JOIN Product ON OrderLine.ProductID = Product.ProductID
        WHERE Product.ProductCode = 'bcem002'
        AND   Customer.CustomerCode = '1000'
        GROUP BY Product.Description,
                 Product.ProductCode,
                 Customer.CustomerCode,
                 Customer.Name,
                 OrderLine.UnitSellPriceInCurrency
        ) tbl1 
        WHERE tbl1.rnk =1 
        ;

Upvotes: 0

Related Questions