Products with Last Purchase Date and Last Purchase ID

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Globalization » Products with Last Purchase Date and Last...

Products with Last Purchase Date and Last Purchase ID

Rate Topic

Display Mode

Topic Options

Author

Message

ceomike

Posted Monday, March 15, 2010 4:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 10, 2010 8:18 PM
Points: 2, Visits: 15

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

Post #882776

GilaMonster

Posted Monday, March 15, 2010 4:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

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 2008, MVP
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

Post #882779

ceomike

Posted Monday, March 15, 2010 4:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 10, 2010 8:18 PM
Points: 2, Visits: 15

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

Post #882787

GilaMonster

Posted Monday, March 15, 2010 6:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 37,742, Visits: 30,021

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #882882

« Prev Topic | Next Topic »

Permissions