T-SQL / SQL Server : help to calculate turnover time for account to become positve from negative balance -


i need calculate turnover time of account become positive negative daily balance. example, account 12345 positive on 04/05/2013, has negative balance on 04/06, 04/07, 04/08 (three days) , on fourth day became positive. develop query calculate turnover time (4 days).

account number  transaction date    daily balance     12345       4/1/2013            304     12345       4/2/2013            -78     12345       4/3/2013            -65     12345       4/4/2013            12     12345       4/5/2013            25     12345       4/6/2013            -345     12345       4/7/2013            -450     12345       4/8/2013            -650     12345       4/9/2013            105     12345       4/10/2013           110     110000      4/1/2013            150     110000      4/2/2013            -15     110000      4/3/2013            -56     110000      4/4/2013            -35     110000      4/5/2013            -15     110000      4/6/2013            106     110000      4/7/2013            500     110000      4/8/2013            -150     110000      4/9/2013            50     110000      4/10/2013           100     55544       4/1/2013            150     55544       4/2/2013            120     55544       4/3/2013            -145     55544       4/4/2013            -250     55544       4/5/2013            15     55544       4/6/2013            20     55544       4/7/2013            40     55544       4/8/2013            50     55544       4/9/2013            -10     55544       4/10/2013           60

i trying following result above table. query apply transactions table having thousands of rows. hence, need build efficient query calculate following results. 

account number  turnover time account become positive 12345           3 12345           4 110000          5 110000          2 55544           3 55544           2

i using ms sql server version 2008.

yes there zeros. , consider positive. mean when balance turning negative 0 or positive, consider turnover.

the solution provided fadi hassan efficient , easy implement. although there 1 challenge when account balance negative in begining, doesn't work. example, when balance -110, -40, 0, 10, 90, 30, 0, -10, -20, 0. not work.

enter image description here

if have better solution, kindly help.

assuming version >= sql server 2005. there 2 things keep in mind. 1. how 0 account balance treated? 2. query scans table 4 times. not efficient.

;with cte as( select t1.accountnumber, t1.transactiondate, t1.dailybalance, nextrowdailybalance #accounts t1 cross apply (select top 1 dailybalance nextrowdailybalance #accounts t2 t1.accountnumber = t2.accountnumber , t1.transactiondate < t2.transactiondate order t2.transactiondate)t (dailybalance < 0 , nextrowdailybalance>0) or (dailybalance > 0 , nextrowdailybalance < 0) ) select a1.accountnumber, datediff(day,a1.transactiondate,t.nrtransactiondate) + 1 turnoverdate cte a1 cross apply (select top 1 transactiondate nrtransactiondate cte a2 a1.accountnumber = a2.accountnumber , a1.transactiondate < a2.transactiondate order a2.transactiondate)t a1.dailybalance>0

Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());
Read more