sql server - Calculate the previous value in T-SQL (update) -


i struggling little, not say, sql logic.

example of data set

wid  ccvnr  amount  reference  seq  month      columiwant=previousamount 11   15946  20      50         1    1/1/2013   null 12   15946  20      50         2    1/2/2013   null 13   15946  20      50         3    1/3/2013   null 14   15946  20      50         4    1/4/2013   null 15   15946  20      50         5    1/5/2013   null 16   15946  20      50         6    1/6/2013   null 35   15946  20      50         1    1/1/2013   null 36   15946  10      50         2    1/2/2013   20 37   15946  10      50         3    1/3/2013   20 38   15946  10      50         4    1/4/2013   20 39   15946  10      50         5    1/5/2013   20 40   15946  10      50         6    1/6/2013   20 88   15946  10      50         1    1/1/2013   20 89   15946  20      50         2    1/2/2013   null 90   15946  25      50         3    1/3/2013   10 91   15946  25      50         4    1/4/2013   10 92   15946  25      50         5    1/5/2013   10 93   15946  25      50         6    1/6/2013   10

the 5 first columns have, want 'calculate' last column: previousamount. problem me every time 6 months (re)loaded it's not enough @ 'changed' value, have take account history there to.
(i first had piece of code cte comparing 1 row next i'm missing history...)

situation here is: client has amount wants pay per month can change: in case changes 20 10 25.

i've uploaded csv-file or dataset i'm working here: https://mega.co.nz/#!ozhc0rxb!czzmx3yr6kx1_1n9scunqwjnqozdguxte47iopklg-e

we using sql server 2008 r2. there 20m rows in table. maybe cursor not best option(?)

thanks lot in advance !! l


update: added wid column because original data set holds wid column. @mark bannister logic @ 'amount' , when month amount changed user: * next months updated when 'new amount' * calculate previous amount = amount before

@love2learn comment. there lots more columns have know it's not easy add more columns in stackoverflow code block. therefore uploaded csv file can find link. relevant column have added 'wid'.

normally there should enough info , columns calculate previous row. in advance!

you need customer number of sort -- identify groups. custid:

select t.*,        (select top 1                 amount t t2 t2.custid = t.custid , t2.month < t.month order t2.month        ) prevamount t

Comments

Post a Comment

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

Image
i have script tag in code, keyboard shortcut comment/uncomment script tag in sublime. after selecting script open , close tag. <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script> i pressing ctrl + shift + / , getting this /*<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script>*/ also pressed ctrl + / , got this // <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> // </script> instead of <!-- <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script> --> script open , close tag in 2 lines not in 1 line , not have spaces too. for mac,high light lines want comment. hold 'command , / ' commented. for windows: high line or select lines ,then hold ctrl + / if not work: ...
Read more

post - imageshack API cURL -

i credentials correct. when send image through form this:(action="http://api.imageshack.com/v2/images/"). working fine when tried send $ch = curl_init($url); failed. code: enter code here <form action="" method="post" enctype="multipart/form-data"> select image upload: <input type="file" name="file" id="file" multiple> <input type="text" name="auth_token" id="auth_token" value="xxxxxxxxxxxxxxxx"> <input type="text" name="api_key" id="api_key" value="xxxxxxxxxxxxxxx"> <input type="submit" value="upload image" > </form> $url = ' http://api.imageshack.com/v2/images/ '; $temp = $_files["file"]['tmp_name']; echo $temp."<br>" ; $ch = curl_init($url); curl_setopt($ch, curlopt_post, true); curl...
Read more

dataset - MPAndroidchart returning no chart Data available -

i having problems mpandroidchart. adding temperature , humidity data chart, displays "no chart data available". here can see bug is? super grateful! //create data //set size of data int size = list.size(); if (size > 24){ size = 24;} //create lists form temp , hum arraylist<entry> temps = new arraylist<entry>(); arraylist<entry> hums = new arraylist<entry>(); //fill list temp values (int = 0; < size; i++){ entry value = new entry(math.round(list.get(list.size()-size + i).temp), (size-i)); temps.add(value); } //fill list hum values (int = 0; < size; i++){ entry value = new entry(math.round(list.get(list.size()-size + i).hum), (size-i)); hums.add(value); } ...
Read more