last price

[Ofina]

I have a list of ordered items. I would like a way to display the most-recent price for each item.
unit price based on min(order_date) or something like that.
Can I do this?

I also want to know if there is an easy way of having it display percent of total other than the brute force method. I think the answer is no.
I can put it into excel and do it there, but if I can do it in sql, all the better.

 

[gmmastros]

Can you show same sample data and expected results. I suspect that this will be relatively simple and straight forward to do in SQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ofina]

I can't figure out how to format this like a table.
Sample data

item order date QTY PRICE
A 2/1/2012 2 3.40
A 4/1/2012 5 2.50
B 6/1/2012 7 8.50
B 3/1/2012 5 4.23
C 7/1/2012 3 6.22
C 8/1/2012 5 7.42
C 5/1/2012 7 2.57

Result I want

item total qty most recent price percent qty of total
A 7 2.50 20.59
B 12 8.50 35.29
C 15 7.42 44.12

 

[gmmastros]

Here's one way....

Declare @Sample Table(Item VarChar(20), OrderDate DateTime, QTY Int, Price Decimal(10,2))

Insert Into @Sample Values('A',	'2/1/2012',	2,	 3.40 )
Insert Into @Sample Values('A',	'4/1/2012',	5,	 2.50 )
Insert Into @Sample Values('B',	'6/1/2012',	7,	 8.50 )
Insert Into @Sample Values('B',	'3/1/2012',	5,	 4.23 )
Insert Into @Sample Values('C',	'7/1/2012',	3,	 6.22 )
Insert Into @Sample Values('C',	'8/1/2012',	5,	 7.42 )
Insert Into @Sample Values('C',	'5/1/2012',	7,	 2.57 )

;With Data As
(
  Select  Item, 
          Price, 
          Row_Number() Over (Partition By Item Order By OrderDate [!]DESC[/!]) As RowId
  From    @Sample
), TotalQty As
(
  Select  Sum(Qty) As TotalQty From @Sample
), ItemQuantities As
(
  Select   Item,
           Sum(Qty) As ItemQty
  From     @Sample
  Group By Item
)
Select  Data.Item, 
        ItemQuantities.ItemQty As TotalQty, 
        Data.Price, 
        Convert(Decimal(5,2), 100.0 * ItemQty / TotalQty) As PercentOfTotal
From    Data
        Cross Join TotalQty
        Inner Join ItemQuantities
          On Data.Item = ItemQuantities.Item
Where   Data.RowId = 1
Order By Data.Item

Note that I create a table variable named @Sample and then dummy up your sample data in to the table variable. If you are satisfied that this returns the correct results, you can remove the temp table and change every place you see @Sample to your actual table name.

Personally, I suspect this query is just a little bit wrong. You wanted the "last price" which would actually be the one with the Max(OrderDate), not the min. The way the query is written, it matches your expected output exactly. If my assumption is correct, and you want the max(OrderDate), then remove the [!]DESC[/!] from the query above.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ofina]

Hey that does work! Thanks.
Though it appears that the only way to do the percent of the whole is indeed by brute force.
I do want max(OrderDate) and that is what you gave me.