sql server - ms sql temporal table find history on column where last changed -
i need query on temperal table's history table, find last time column value changed , difference vs now. there other changes recorded in table need find change column data changed, find out how , when changed
my temperal history table looks this
id |price |lastmodifieddate |other data ------ |---------------|------------------|-------- 696733 |9995 |08/nov/2016 09:30 |1 -other change 696733 |9995 |06/nov/2016 09:28 |2 -price change -current price 696733 |10995 |30/oct/2016 09:29 |2 -other change - prev price 696733 |10995 |29/oct/2016 09:29 |3 -other change 696733 |10995 |26/oct/2016 10:10 |4 -other change 696733 |10995 |26/oct/2016 08:42 |5 -other change 696733 |10995 |25/oct/2016 10:11 |6 -price change - 696733 |11595 |22/oct/2016 09:50 |6 -other change - old old price 696733 |11595 |21/oct/2016 15:26 |7 -other change
so id looking return 9995 current price , 10995 previous price , date of change 06/nov/2016 09:28 , ignore other previous price changes. need filter on results check if change 28 days ago need solution can put sub query or cross apply
i been trying use "over(order" avoid using lost of nested queries
thanks in advance
edit
my sql fiddle example http://sqlfiddle.com/#!6/05db1/7
note sql fiddle doesn't support temperal tables had mimic bit
you can use outer apply
order by
recent history record different price current price (assuming current price in pricing
table).
select p.id, p.price current_price, ph.price prev_price, ph.lastmodifieddate prev_price_date [pricing] p outer apply ( select top 1 ph1.price, ph1.lastmodifieddate [pricehistory] ph1 ph1.id = p.id , ph1.price <> p.price order ph1.lastmodifieddate desc ) ph
Comments
Post a Comment