Hello I have the following data:
ID, OrderType, Amount, Price, Time 1, Buy, 10, 1, 10/01/2018 10:30 AM 2, Buy, 2, 1, 10/01/2018 11:30 AM 3, Sell, 5, 3, 10/01/2018 01:30 PM 4, Sell, 3, 6, 11/01/2018 10:30 AM 5, Buy, 13, 2, 11/01/2018 11:30 AM 6, Sell, 3, 3, 12/01/2018 10:30 AM 7, Sell, 11, 2, 13/01/2018 10:30 AM
What I need to do is to get the following:
PosNo, OpenTime, OpenAmount, OpenPrice, CloseAmount, ClosePrice, CloseTime 1, min(time), 10, 1, 10, (5*3+3*6+2*3), max(time) 2, min(time), 2, 1, 2, (1*3+1*2), max(time) 3, min(time), 13, 2, 10, (10*2), max(time)
For good looking of the tables I have replaced the times with text, but they should look like this: min(time) = first buy order time, max(time)=last sell order. So the basic idea is to get one record for each buy order and to fill the Close values based on the sell records. As you can see most of the times there will be few buy orders and the sell orders are not for the same amount of the buy order, so it is hard to match them in another way, but to sum the sell orders until they are more then the buy order, and then remove the extra value to be used in the next buy order.
Hope I have explained what I am looking for and Hope you can help me, as I am looking into the internet for solution, but have no luck for over a week now. Probably I do not know how to ask the question, but at least I hope with this explanations it will be easy to find a solution.