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 B 02-Mar-2015 2 B 02-Mar-2013 10 then result should itemCode PurchaseDate Price A 02-Mar-2011 6 B 02-Mar-2015 2 How to do this Thanks Basit.

since you have two price on the same day for the same item, it will show both of them..if you keep time, it will show the latest one..

Dear, if data something like this Trn_Purchase2 Itemcode PurchaseDate Price A 01/03/2010 4.000 A 02/03/2011 6.000 A 01/03/2010 4.000 A 02/03/2011 10.000 B 02/03/2015 2.000 B 02/03/2013 10.000 B 02/03/2015 2.000 B 02/03/2013 10.000 then what will be the query. Thanks Basit

try this : select p.ItemCode ,p.PurchaseDate,p.Price from dbo.Trn_Purchase p inner join (select ItemCode,max(PurchaseDate) as Maxdate from dbo.Trn_Purchase group by ItemCode) t on p.ItemCode = t.ItemCode and p.PurchaseDate = t.Maxdate

Answers

How to get last purchase price depend upon date in MSAccess

Basit Khan

917

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
B	02-Mar-2015	2
B	02-Mar-2013	10

then result should

itemCode	PurchaseDate	Price
A	02-Mar-2011	6
B	02-Mar-2015	2

How to do this

Thanks

Basit.

Answers (3)

Next Recommended Forum

sql server 2008 r2 report rendering issue

Error Deadlock Victim

Leaderboard

View all

Vijayaragavan S

Abiola David

Alagunila Meganathan

View all

Yesterday's leader

Vijayaragavan S

C#, UWP, .NET, Salesforce, Power Platform

Namakkal (India)

4.1m

49.2k

Member of the month

Shafaet Hossain

C#, .NET, ASP.NET, Blazor

Dhaka (Bangladesh)

700

9.8k

1.3k

Speaker of the month

Shubhankar Banerjee

Blockchain, Solidity, Polygon, C#, NodeJS, HTML, C Language, C++, JavaScript, CSS, ReactJS, MySQL

Kanpur (India)

157

117.9k

11.9k

500

Upcoming events

View all

Suggested for you

View all

Saurabh Prajapati

Chris Love

Diptiranjan Sutar

Najath Risni

Arindam Dawn

Forum Statistics

Please welcome our newest member ttrustefy .
3,078,474 users have contributed to 147,418 threads and 483,404
In the past 24 hours, we have 14 new threads, 34 new posts, and 82 new users.
In last week, the most popular thread is 'Get 24 months rollover value from database'.