Products with Last Purchase Date and Last Purchase ID

  • ceomike

    SSC Enthusiast

    Points: 128

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ceomike

    SSC Enthusiast

    Points: 128

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    I'm going to assume you didn't read the article.... Please do so.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply