sql - Find the Last Price that is different from the Current Price -


i have sales transactions in sql server table this:

itemnumber, trxdate, unitprice abc, 1/1/2013, 10.00 abc, 2/1/2013, 10.00 abc, 3/1/2013, 13.00 abc, 4/1/2013, 14.00 abc, 5/1/2013, 14.00 xyz, 1/1/2013, 18.00 xyz, 2/1/2013, 18.00 xyz, 3/1/2013, 20.00 xyz, 4/1/2013, 20.00 xyz, 5/1/2013, 20.00 

i need stored procedure produce output this

itemnumber, lastprice, priorprice abc, 14.00, 13.00 xyz, 20.00, 18.00 

assmunig sql server 2005+:

;with cte (     select  *,             rn=row_number() over(partition itemnumber order trxdate desc)     (  select  itemnumber,                     max(trxdate) trxdate,                     unitprice             yourtable             group itemnumber,                      unitprice) ) select  itemnumber,         min(case when rn = 1 unitprice end) lastprice,         min(case when rn = 2 unitprice end) priorprice cte group itemnumber 

Comments

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

web - SVG not rendering properly in Firefox -

java - JavaFX 2 slider labelFormatter not being used -