jan s via sqlmonstercom

I have a db which stores all my sales transactions.
The task is to extract the buy price(price) for each item(ItemCode) where the
date is the latest for each item.

For Example:
Sales Table
ItemCode Qty Price Date
-------------- ----- -------- --------
XYZ 2 23.50 12/03/05
XYZ 3 23.50 13/03/05
XYZ 2 99.99 14/03/05
ABC 14 88.88 01/01/05
RDB 12 77.30 21/04/05
RDB 23 23.35 02/05/05

I would then require only

ItemCode Qty Price Date
-------------- ----- -------- --------
XYZ 2 99.99 14/03/05
ABC 14 88.88 01/01/05
RDB 23 23.35 02/05/05

How do i write a query to extract the data?
Need help with a project of mine....really stuck with this one..Thank you

hugo kornelis

Hi Jan,

Method #1:
---------
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
WHERE NOT EXISTS
(SELECT *
FROM Sales AS b
WHERE b.ItemCode = a.ItemCode
AND b.[Date] > a.[Date])

Method #2:
---------
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
WHERE a.[Date] =
(SELECT MAX(b.[Date])
FROM Sales AS b
WHERE b.ItemCode = a.ItemCode)

Method #3:
---------
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
INNER JOIN (SELECT ItemCode, MAX([Date]) AS MaxDate
FROM Sales
GROUP BY ItemCode) AS b
ON b.ItemCode = a.ItemCode
AND b.MaxDate = a.[Date]

(And there might even be more methods...)

If performance is important, then test each of these queries a few times
and use the one that's the fastest. Otherwise, use the one that you find
the easiest to understand, as you'll have to maintain it later.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

jan s via sqlmonstercom

Thanks Hugo..Much appreciated


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-mseq/200508/1