group by - Creating realized trades and unrealized trades table from trades data using mysql -


consider have table this

ccode | stockname | tradedate  | transactiontype| qty 2089  | 'abc'     |'2012-09-1' | 'buy'          | 200 2089  | 'def'     |'2012-09-5' | 'sell'         | 500 3412  | 'abc'     |'2012-07-12'| 'buy'          | 1200 2089  | 'abc'     |'2012-09-7' | 'buy'          | 200 2089  | 'abc'     |'2012-09-8' | 'sell'         | 100  3412  | 'def'     |'2012-06-5' | 'buy'          | 200 3412  | 'abc'     |'2012-08-4' | 'buy'          | 400 2089  | 'def'     |'2012-09-5' | 'buy'          | 500 3412  | 'def'     |'2012-09-1' | 'sell'         | 70 3412  | 'def'     |'2012-09-2' | 'sell'         | 80 3412  | 'def'     |'2012-09-3' | 'sell'         | 50 2089  | 'abc'     |'2012-10-2' | 'sell'         | 400  

following 2 tables, realized trades , unrealized trades our desired output

realized trades

ccode | stcokname | buydate     | buyqty    | selldate  | sellqty 2089  | 'abc'     | '2012-09-1' | 100       |'2012-09-8'| 100 2089  | 'abc'     | '2012-09-1' | 100       |'2012-10-2'| 100 2089  | 'abc'     | '2012-09-7' | 200       |'2012-10-2'| 200 2089  | 'def'     | '2012-09-5' | 500       |'2012-09-5'| 500 3412  | 'def'     | '2012-06-5' | 70        |'2012-09-1'| 70 3412  | 'def'     | '2012-06-5' | 80        |'2012-09-2'| 80 3412  | 'def'     | '2012-06-5' | 50        |'2012-09-3'| 50 

unrealized trades

ccode | stcokname | buydate     | buyqty    | selldate  | sellqty 2089  | 'abc'     |             |           |'2012-10-2'| 100 3412  | 'abc'     |'2012-07-12' |   1200    |           |  3412  | 'abc'     |'2012-08-4'  |   400     |           |  

i want create realized , unrealized table trades data using fifo method. fifo method, first oldest stock squared off new stock squared off. can in python: 1.sorting based on tradedate 2.iterating on data each group (ccode , stockname) , taking minimum of buy , sell qty , squaring off. move next qty.

python taking lot of time, can in mysql.


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -