How to get last purchase price

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Dear,

I have table called Trn_Purchase
fields are
ItemCode
ItemDesc
PurchaseDates
Price

I want to get the last purchase price depend on date

table have e.g below data

ItemCode PurchaseDate Price
A 01-Mar-2010 4
A 02-Mar-2011 6
then result should

itemCode PurchaseDate Price
A 02-Mar-2011 6

How to do this

Thanks
Basit.

What I have tried:

I did below

SQL

   SELECT
 p.*
FROM
 Trn_Purchase p,
 (SELECT * FROM Trn_Purchase) m
WHERE 
 p.Itemcode = m.Itemcode
 and p.[Purchase Date] = m.[Purchase Date]

Posted 15-Apr-16 7:35am

basitsar

Updated 15-Apr-16 19:20pm

Blutfaust

v4

Add a Solution

Comments

Richard Deeming 15-Apr-16 13:42pm

You've tagged the question as both "Access" and "SQL Server 2008 R2". Which is it?

Philippe Mori 15-Apr-16 16:44pm

Learn to format your question.

Philippe Mori 15-Apr-16 16:52pm

I don't think it is possible to do efficient request for that. I think that usually one would use distinct table for actual data that for historical data otherwise it would penalize most requests even though you would rarely use old data.

Philippe Mori 15-Apr-16 16:59pm

By the way, your question is not clear. If you have multiple item code, then your sample data should show multiple codes too and it should show the desired result in that case.

It make no sense to provide simplistic data if you want complex query. At least, you should provide data that could prove if a query always give the desired result.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-04-15T08:35:00

Solution 1

Try:

SQL

SELECT TOP 1 * FROM Trn_Purchase ORDER BY PurchaseDate DESC

"yes each item code"
If you want a specific output, then give specific examples, and explain exactly what you want: it means we can reply with a specific solution!

Try:

SQL

SELECT ItemCode, PurchaseDate, Price 
FROM (SELECT  *, ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER By PurchaseDate DESC) AS rn
      FROM Trn_Purchase) x
WHERE rn = 1

Posted 15-Apr-16 8:35am

OriginalGriff

Updated 15-Apr-16 22:52pm

v2

Comments

Philippe Mori 15-Apr-16 16:53pm

I would think that OP would want the latest price for each item code...

basitsar 16-Apr-16 4:02am

yes each item code

OriginalGriff 16-Apr-16 4:52am

Answer updated

basitsar 16-Apr-16 6:18am

thanks alot.
is this work in msaccess.

SELECT ItemCode, PurchaseDate, Price
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER By PurchaseDate DESC) AS rn
FROM Trn_Purchase) x
WHERE rn = 1

OriginalGriff 16-Apr-16 6:20am

You're welcome!

basitsar 16-Apr-16 8:30am

Dear im asking in msaccess this query also work?

basitsar 19-Apr-16 5:39am

Is there any update for msaccess