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

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

asp.net - Problems sending emails from forum -