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

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

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

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more