database - Sum balance via Price + Balance From previous row -


i have oracle database 11g table 'transaction' . want balance.

i have now:

|   date   |   description    | min_plus | amount | balance | | 4-7-2015 | gift dad    | +        | 45,00  |       0 | | 5-7-2015 | gift sister   | -        | 3,00   |       0 | | 6-7-2015 | gift aunt   | +        | 2,50   |       0 |

i'm looking for:

|   date   |   description    | min_plus | amount | balance | | 4-7-2015 | gift dad    | +        | 45,00  |  45,00  | | 5-7-2015 | gift sister   | -        | 3,00   |  42,00  | | 6-7-2015 | gift aunt   | +        | 2,50   |  44,50  |

is possible virtual colmn, better use pl/sql or not possible do?

you can running total in plain sql, when select data display. no need create balance column or pl/sql trick. oracle windowing functions nifty use! in case, want sum() amount column first row current row in order of transaction date. can express in surprisingly plain language:

with sample_data (     select to_date('04-07-2015', 'mm-dd-yyyy') datecol, 'gift dad' description, '+' min_plus, 45 amount dual union     select to_date('05-07-2015', 'mm-dd-yyyy'), 'gift sister', '-', 3 dual union     select to_date('06-07-2015', 'mm-dd-yyyy'), 'gift aunt', '+', 2 dual)  select datecol, description, min_plus, amount, sum(case when min_plus = '-' -1 * amount else amount end)    on (order datecol rows between unbounded preceding , current row) balance sample_data order datecol;

sql fiddle example

a few notes: window function sum() on (partition ... order ... range) magic. in case not partitioning (restarting calculation) data set, can skip that. including order by in window ensure summing values in correct order, expressing order by entire value set normal. , can google range clause, can see it's rather plain language.

by way, using char transaction sign awkward. suggest storing signed integer, 1 , -1, can multiply amount transaction sign column.


-

Comments

Post a Comment

Popular posts from this blog

c# - Binding a comma separated list to a List<int> in asp.net web api -

the default binder in web api expecst http://url.com/webapi/report/?pageids=3243&pageids=2365 to bind public ihttpactionresult report(list<int> pageids){ // exciting webapi code} i wish bind http://url.com/webapi/report/?pageids=3243,2365 as running out of space in url get . i have created class public class commaseparatedmodelbinder : system.web.http.modelbinding.imodelbinder { public bool bindmodel(httpactioncontext actioncontext, modelbindingcontext bindingcontext) { //binding in here } } and registered in webapiconfig.cs var provider = new simplemodelbinderprovider( typeof(list<int>), new commaseparatedmodelbinder()); config.services.insert(typeof(modelbinderprovider), 0, provider); i have altered method signature use model binder so public ihttpactionresult report( [modelbinder] list<int> pageids){ // exciting webapi code} however break point in binder not being
Read more

Delphi 7 and decode UTF-8 base64 -

in delphi 7, have widestring encoded base64(that received web service widestring result) : pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg== when decoded it, result not utf-8: <?xml version="1.0"?> <string>طھط³طھ</string> but when decoded base64decode.org, result true : <?xml version="1.0"?> <string>تست</string> i have use encddecd unit decodestring function. the problem have using decodestring . function, in delphi 7, treats decoded binary data being ansi encoded. , problem text utf-8 encoded. to continue encddecd unit have couple of options. can switch decodestream . instance, code produce utf-8 encoded text file data: {$apptype console} uses classes, encddecd; const data = 'pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg=='; var input: tstringstream; output: tfilestream; begin input := tstringstream.create(data); try output := tfilestream.
Read more

html - Is there any way to exclude a single element from the style? (Bootstrap) -

so, have img logo, overlaid on div styled background-image: url("...") , , both elements inside 2 div classes .to-color , .to-overlay respectively, black transparent overlay. is there way exclude logo style given these 2 classes? here's jsfiddle example: link html , css example: - html - <div class="to-color"> <div class="to-overlay"> <div class="respo"> <div class="row"> <div class="col-xs-4 col-xs-offset-4 text-center"> <img src="http://ansonalex.com/wp-content/uploads/2011/06/google-logo-768x260.png" alt="..." class="img-responsive"> </div> </div> </div> </div> </div> - css - .to-color { background-color: #000; } .to-overlay { opacity : 0.5; } .respo { background-image: url("https://source.unsplash.com/category/objects/1600x900"); background-position: center center;
Read more