Products with Last Purchase Date and Last Purchase ID

mikewin86 · #1 (**permalink**) 03-15-10, 05:48

Hello ,

I would like to query from SQL Server 2000 Database.I have got two tables. They are Purchase and PurchaseDetails. I would like to get product records with Last Purchase ID and Last Purchase Date but I can't query with the following statements.So please help me.

SELECT TOP 100 PERCENT dbo.Purchase.PurchaseID AS LastOfPurchaseID, dbo.PurchaseDetails.ProductID, MAX(dbo.Purchase.PurchaseDate) AS LastOfPurchaseDate FROM dbo.Purchase INNER JOIN dbo.PurchaseDetails ON dbo.Purchase.PurchaseID = dbo.PurchaseDetails.PurchaseID GROUP BY dbo.PurchaseDetails.ProductID, dbo.Purchase.PurchaseDate,dbo.Purchase.PurchaseID ORDER BY MAX(dbo.Purchase.PurchaseDate) DESC

pootle flump · #2 (**permalink**) 03-15-10, 06:07

I assume by last you mean greatest.

That query will work if your greatest purchase ID correlates with your greatest purchase date. Otherwise it won't.

mikewin86 · #3 (**permalink**) 03-15-10, 06:31

In fact , that query must work but it retrieve all records and all products(i.e. not only last Purchase Date but also old Purchase Date).

pootle flump · #4 (**permalink**) 03-15-10, 06:46

I see.
So is this what you need: "For each product I want to know the greatest purchase date and the ID for that purchase"?

mikewin86 · #5 (**permalink**) 03-15-10, 06:51

yes friend. here my sample data for this.

Purchase Table includes the following fields.

PurchaseID
PurchaseDate
Remark

Purchase Deatails Table includes the following fields.

PurchaseDetailsID
PurchaseID
ProductID
BatchNo
Quantity
Price
Amount

here sample data

PurchaseID PurchaseDate
100 12/1/2009
101 12/20/2009
102 12/30/2009

PurchaseDetailsID PurchaseID ProductID Quantity BatchNo
200 100 20 100 A10000
201 101 20 120 B10001
202 102 20 50 D01111

I would like to get product with Last Purchase Date and Last Purchase ID as below.

PurchaseID Date Product Qty BatchNo
102 12/30/2009 20 50 D01111

pootle flump · #6 (**permalink**) 03-15-10, 06:53

Code:

SELECT  Purchase.PurchaseID         AS LastOfPurchaseID
      , PurchaseDetails.ProductID
      , Purchase.PurchaseDate       AS LastOfPurchaseDate 
FROM    dbo.Purchase 
INNER JOIN 
        dbo.PurchaseDetails 
ON dbo.Purchase.PurchaseID      = dbo.PurchaseDetails.PurchaseID
INNER JOIN 
        (
            SELECT  PurchaseDetails.ProductID
                  , GreatestPurchaseDate    = MAX(Purchase.PurchaseDate)
            FROM    dbo.PurchaseDetails
            GROUP BY PurchaseDetails.ProductID
        ) AS LastPurchaseByProduct
ON  LastPurchaseByProduct.ProductID             = PurchaseDetails.ProductID
AND LastPurchaseByProduct.GreatestPurchaseDate  = PurchaseDetails.PurchaseDate

mikewin86 · #7 (**permalink**) 03-15-10, 07:10

Thanks friend. But when I run that statements , it give following error.

"The column prefix 'Purchase' does not match with a table name or alias name used in the query."

pootle flump · #8 (**permalink**) 03-15-10, 07:12

Code:

SELECT  Purchase.PurchaseID         AS LastOfPurchaseID
      , PurchaseDetails.ProductID
      , Purchase.PurchaseDate       AS LastOfPurchaseDate 
FROM    dbo.Purchase 
INNER JOIN 
        dbo.PurchaseDetails 
ON dbo.Purchase.PurchaseID      = dbo.PurchaseDetails.PurchaseID
INNER JOIN 
        (
            SELECT  PurchaseDetails.ProductID
                  , GreatestPurchaseDate    = MAX(Purchase.PurchaseDate)
            FROM    dbo.Purchase 
            INNER JOIN 
                    dbo.PurchaseDetails 
            ON dbo.Purchase.PurchaseID      = dbo.PurchaseDetails.PurchaseID
            GROUP BY PurchaseDetails.ProductID
        ) AS LastPurchaseByProduct
ON  LastPurchaseByProduct.ProductID             = PurchaseDetails.ProductID
AND LastPurchaseByProduct.GreatestPurchaseDate  = PurchaseDetails.PurchaseDate

Internet Services

Web Development

Digital Marketing

Consumer Tech