Most recent data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Most recent data

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-06-12 : 14:42:16

Hi All,

I have a table with the following fields.

Date(Datetime),ZoneID(varchar), ProductID(varchar),Price(float),Cost(float),Units(big int)

Each record tells the price,cost,units of a product sold in a particular zone on a particular date.

A product may or may not sold on a particular date.

I want to see the most recent (most latest date available) information of each product in each zone.

I want the following fields:

ZoneID,ProductID, MostRecentPrice, MostRecentCost, MostRecentUnits, Date

Where Date will be the most recent date on which that product (in that particular zone) was sold.

Thanks a million in adavance.

-Zeeshan

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-12 : 15:45:34

ORDER BY [Date] DESC?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-13 : 02:26:05

[code]SELECT t.ZoneID
,t.ProductID
,t.Price AS MostRecentPrice
,t.Cost AS MostRecentCost
,t.Units AS MostRecentUnits
,t.Date
FROM table t
INNER JOIN (
SELECT ZoneID, ProductID, MAX(Date) AS MaxDate
FROM table o
GROUP BY ZoneID, ProductID
) AS dt
ON t.ZoneID = dt.ZoneID AND t.ProductID = dt.ProductID AND t.Date = dt.MaxDate[/code]

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources