Products with Last Purchase Date and Last Purchase ID

  • ceomike

    SSC Journeyman

    Points: 98

    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: 1004424

    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 Journeyman

    Points: 98

    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: 1004424

    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