Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Products with Last Purchase Date and Last Purchase ID

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    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).

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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"?

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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

  7. #7
    Join Date
    Mar 2010
    Posts
    4
    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."

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •