sql - Calculation of balance after each transaction -

-

i have table this:

cust_id acc_no trans_id  trans_type amount 1111    1001    10   credit     2000.0   1111    1001    11   credit     1000.0 1111    1001    12   debit      1000.0   2222    1002    13   credit     2000.0   2222    1002    14   debit      1000.0

i want hive query or sql query every transaction done customer balance should calculated so.

i want output follows:

cust_id acc_no trans_id  trans_type amount      balance 1111.0  1001.0  10.0     credit    2000.0   2000.0 1111.0  1001.0  11.0     credit    1000.0   3000.0 1111.0  1001.0  12.0     debit     1000.0   2000.0 2222.0  1002.0  13.0     credit    2000.0   2000.0 2222.0  1002.0  14.0     debit     1000.0   1000.0

i've tried

select *    (select cust_id,                 acc_no,                 trans_id,                 trans_type,                 amount,                 case                   when trim(trans_type) = 'credit' ball =                   trim(bal) + trim(amt)                   else ball = trim(bal) - trim(amt)                 end            ban) l;

this query trick : 

select t1.cust_id,t1.acc_no,t1.trans_id,t1.trans_type,t1.amount,        sum(t2.amount*case when t2.trans_type = 'credit' 1                               else -1 end) balance table1 t1 inner join table1 t2 on t1.cust_id = t2.cust_id ,                          t1.acc_no = t2.acc_no ,                          t1.trans_id >= t2.trans_id group t1.cust_id,t1.acc_no,t1.trans_id,t1.trans_type,t1.amount

see sqlfiddle : http://www.sqlfiddle.com/#!2/3b5d8/15/0

edit : sql fiddle

mysql 5.5.32 schema setup:

create table table1     (`cust_id` int, `acc_no` int, `trans_id` int,       `trans_type` varchar(6), `amount` int) ;  insert table1     (`cust_id`, `acc_no`, `trans_id`, `trans_type`, `amount`) values     (1111, 1001, 10, 'credit', 2000.0),     (1111, 1001, 11, 'credit', 1000.0),     (1111, 1001, 12, 'debit', 1000.0),     (2222, 1002, 13, 'credit', 2000.0),     (2222, 1002, 14, 'debit', 1000.0) ;

query 1:

select t1.cust_id,t1.acc_no,t1.trans_id,t1.trans_type,t1.amount,        sum(t2.amount*case when t2.trans_type = 'credit' 1                               else -1 end) balance table1 t1 inner join table1 t2 on t1.cust_id = t2.cust_id ,                          t1.acc_no = t2.acc_no ,                          t1.trans_id >= t2.trans_id group t1.cust_id,t1.acc_no,t1.trans_id,t1.trans_type,t1.amount

results:

| cust_id | acc_no | trans_id | trans_type | amount | balance | |---------|--------|----------|------------|--------|---------| |    1111 |   1001 |       10 |     credit |   2000 |    2000 | |    1111 |   1001 |       11 |     credit |   1000 |    3000 | |    1111 |   1001 |       12 |      debit |   1000 |    2000 | |    2222 |   1002 |       13 |     credit |   2000 |    2000 | |    2222 |   1002 |       14 |      debit |   1000 |    1000 |

Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more