sql server - Price history based on date -

sql server - Price history based on date -

have requirement list cost alter single output.

my table has info below

let's phone call table price_hist

itemid cost price_change_date 111a112 1,000.00 10/03/2014 111a114 1,111.00 10/03/2014 111a11 1,111.00 10/03/2014 111a111 1,000.00 10/03/2014 111a114 2,222.00 10/09/2014 111a111 2,222.00 10/09/2014

need help query output below.

itemid last_modified current_price last_change last_change1 last_change2 111a112 10/03/2014 1,000.00 na na na 111a11 10/03/2014 1,111.00 na na na 111a114 10/09/2014 2,222.00 1,111.00 na na 111a111 10/09/2014 2,222.00 1,000.00 na na

first time user :-( unable format code

your question tagged mysql.

this mysql solution:

select itemid, max(price_date) last_modified, max(case when rn = 1 cost end) current_price, max(case when rn = 2 cost end) last_change, max(case when rn = 3 cost end) last_change1, max(case when rn = 4 cost end) last_change2 ( select @rn := case when @itemid = itemid @rn + 1 else 1 end rn, @itemid := itemid itemid, price, price_date price_hist cross bring together (select @rn := 0, @itemid := '') t order itemid, price_date desc ) x grouping itemid order last_modified, itemid

this uses variables mimic row_number() functionality available in other databases, partitioning on itemid. uses conditional aggregation pivot 4 latest prices each item.

if you're using sql server, can utilize row_number()

this sql server solution:

select itemid, max(price_date) last_modified, max(case when rn = 1 cost end) current_price, max(case when rn = 2 cost end) last_change, max(case when rn = 3 cost end) last_change1, max(case when rn = 4 cost end) last_change2 ( select row_number() on (partition itemid order itemid, price_date desc) rn, itemid, price, price_date price_hist ) x grouping itemid order last_modified, itemid

sql-server