How to show last purchased rate in all months of output? New

Question

How to show last purchased rate in all months of output? New

Viewing 3 posts - 16 through 17 (of 17 total)

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

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

VSP (11/13/2014)
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is needed
In that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
David is right, MAX(rate) with Over/Partition/OrderBy on Date will not work because MAX gives you the max rate within the partition but not necessarily the rate for the most recent date within the partition.
Instead ROW_NUMBER() with partition/orderby-on-Date/Qualify, as David presented, produces the expected result.

I agree. My point was that we need some requierements clarification as the desired output can be produced in many ways.

If we need max() aggregation with windowing under 2008, besides ROW_NUMBER() we can also opt to APPLY, which is logically equal and has perfomance not much worse then genuine windowing.

select a.product, m.rate, a.QTY, a.Bill_date, a.total_val

from dbo.test a

cross apply (select top(1) b.rate

from dbo.test b

where b.product = a.product

order by b.Bill_date desc

, b.rate desc

) m

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

serg-52 (11/14/2014)
VSP (11/13/2014)
Eirikur Eiriksson (11/12/2014)
Koen Verbeeck (11/12/2014)
David Burrows (11/12/2014)
However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is needed
In that case an ORDER BY on date should be added to the OVER clause, but this only works from SQL Server 2012 and up.
Works on everything from 2K5 onward as it is a part of the initial (read limited) window function implementation in SQL Server.
😎
David is right, MAX(rate) with Over/Partition/OrderBy on Date will not work because MAX gives you the max rate within the partition but not necessarily the rate for the most recent date within the partition.
Instead ROW_NUMBER() with partition/orderby-on-Date/Qualify, as David presented, produces the expected result.
I agree. My point was that we need some requierements clarification as the desired output can be produced in many ways.
If we need max() aggregation with windowing under 2008, besides ROW_NUMBER() we can also opt to APPLY, which is logically equal and has perfomance not much worse then genuine windowing.
select a.product, m.rate, a.QTY, a.Bill_date, a.total_val
from dbo.test a
cross apply (select top(1) b.rate
from dbo.test b
where b.product = a.product
order by b.Bill_date desc
, b.rate desc
) m

Quick points, no need for a max if using row_number, order by xxx desc will return the latest entry first. Aggregating the values is also wrong as the OP's request was "like i ve to show the price of latest price in all of months product billed". Cross apply in this case is more than double the effort of a straight row_number partitioned on T.product,YEAR(T.Bill_date), MONTH(T.Bill_date) as is invokes two separate scans each with a very expensive sort operators. Last but not least, proper indexing would help in (almost) all cases;-)

😎

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

Eirikur Eiriksson (11/14/2014)
Quick points, no need for a max if using row_number, order by xxx desc will return the latest entry first. Aggregating the values is also wrong as the OP's request was "like i ve to show the price of latest price in all of months product billed". Cross apply in this case is more than double the effort of a straight row_number partitioned on T.product,YEAR(T.Bill_date), MONTH(T.Bill_date) as is invokes two separate scans each with a very expensive sort operators. Last but not least, proper indexing would help in (almost) all cases;-)
😎

It will. But it's still uknown whether OP needs only those rows with latest price or all the rows must be returned but showing the latest price.

In later case i thing CROSS APPLY is appropriate tool for the task.