Find last purchase price of a product

Question

Post reply

Find last purchase price of a product

2Tall

SSChampion

Points: 14644
More actions
January 30, 2011 at 1:48 am

#233578

Hi below returns a list of unique products (type purchased, purchased assembly) that have been purchased and invoiced.
SELECT DISTINCT pii.Product
FROM PurchaseInvoiceItems AS pii
WHERE pii.Product IN (SELECT p.Product FROM Products AS p WHERE p.Type IN ('B','P'))
The above returns 3000 rows.
I would like to list the last purchase price of the unique product. PurchaseInvoiceItems is related to PurchaseInvoices by PurchaseInvoice. PurchaseInvoices has field 'AccountingDate'. PurchaseInvoiceItems has a field 'ItemValue'.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I think you've been around long enoug to know how it works...

Table DDL, sample data and expected results, please.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

2Tall SSChampion Points: 14644 More actions · Answer 2

Fair point, working on it 🙂

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Take a look at this article that i wrote a little while back.

http://www.sqlservercentral.com/articles/T-SQL/71571/

You just need top(1).

Clear Sky SQL
My Blog[/url]

2Tall SSChampion Points: 14644 More actions · Answer 4

Update: Code removed.

Thanks to all those that took time to review.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper