Net position calculation

I have a table of orders that looks like this:

Ticket  Open Time               Close Time              Type    Size
253199  2010-09-09 20:40:00     2010-09-09 20:41:00     sell    0.1
255406  2010-09-13 19:30:00     2010-09-14 04:30:00     buy     0.1
258089  2010-09-15 07:00:00     2010-09-15 09:15:00     sell    0.1
258197  2010-09-15 09:15:00     2010-09-15 14:50:00     buy     0.1
258203  2010-09-15 09:20:00     2010-09-15 14:50:00     buy     0.1
259659  2010-09-16 04:35:00     2010-09-16 18:35:00     sell    0.1
261065  2010-09-17 07:05:00     2010-09-20 00:02:00     buy     0.1

For each order I would like to get a cumulative total of all orders (of the same Type) that are simultaneously opened. In other words, a sum of all Size of any order that opened before the given order and closed after the given order.

Here is my code:

DROP TABLE
IF EXISTS LongOrders;

CREATE TABLE LongOrders SELECT 
    B.Ticket AS Ticket,
    SUM(B.`Size`)AS `Cumulative Long`
FROM
    `orders`.eurusd_fx AS A
JOIN `orders`.eurusd_fx AS B ON(
    B.`Close Time` >= A.`Open Time`
    AND B.`Open Time` <= A.`Open Time`
    AND A.Type = B.Type
    AND A.Type = 'buy'
)
GROUP BY
    A.`Open Time`
ORDER BY
    A.`Open Time`;

DROP TABLE
IF EXISTS ShortOrders;

CREATE TABLE ShortOrders SELECT
    B.Ticket AS Ticket,
    SUM(B.`Size`)AS `Cumulative Short`
FROM
    `orders`.eurusd_fx AS A
JOIN `orders`.eurusd_fx AS B ON(
    B.`Close Time` >= A.`Open Time`
  AND B.`Open Time` <= A.`Open Time`
    AND A.Type = B.Type
    AND A.Type = 'sell'
)
GROUP BY
    A.`Open Time`
ORDER BY
    A.`Open Time`;

DROP TABLE
IF EXISTS CumLong;

CREATE TABLE CumLong SELECT
    A.*
FROM
    LongOrders AS A
JOIN `orders`.eurusd_fx AS B USING(Ticket);

DROP TABLE
IF EXISTS CumShort;

CREATE TABLE CumShort SELECT
    A.*
FROM
    ShortOrders AS A
JOIN `orders`.eurusd_fx AS B USING(Ticket);

SELECT DISTINCT
    A.*, CumLong.`Cumulative Long`,
    CumShort.`Cumulative Short`
FROM
    `orders`.eurusd_fx AS A
JOIN(CumShort, CumLong)ON(
    A.Ticket = CumLong.Ticket
    OR A.Ticket = CumShort.Ticket
) GROUP  BY A.`Open Time`;

And the output:

+--------+---------------------+------+------+--------+---------------------+------------+-----------------+------开发者_高级运维------------+
| Ticket | Open Time           | Type | Size | Item   | Close Time          | Commission | Cumulative Long | Cumulative Short |
+--------+---------------------+------+------+--------+---------------------+------------+-----------------+------------------+
| 253199 | 2010-09-09 20:40:00 | sell |  0.1 | eurusd | 2010-09-09 20:41:00 | -0.89      |         0.10000 |          0.10000 |
| 255406 | 2010-09-13 19:30:00 | buy  |  0.1 | eurusd | 2010-09-14 04:30:00 | -0.9       |         0.10000 |          0.10000 |
| 258089 | 2010-09-15 07:00:00 | sell |  0.1 | eurusd | 2010-09-15 09:15:00 | -0.91      |         0.10000 |          0.10000 |
| 258197 | 2010-09-15 09:15:00 | buy  |  0.1 | eurusd | 2010-09-15 14:50:00 | -0.91      |         0.10000 |          0.10000 |
| 259659 | 2010-09-16 04:35:00 | sell |  0.1 | eurusd | 2010-09-16 18:35:00 | -0.91      |         0.10000 |          0.10000 |
| 261065 | 2010-09-17 07:05:00 | buy  |  0.1 | eurusd | 2010-09-20 00:02:00 | -0.92      |         0.10000 |          0.10000 |
| 262121 | 2010-09-20 03:00:00 | sell |  0.1 | eurusd | 2010-09-20 05:55:00 | -0.91      |         0.10000 |          0.10000 |
| 262192 | 2010-09-20 05:50:00 | buy  |  0.1 | eurusd | 2010-09-20 09:50:00 | -0.92      |         0.10000 |          0.10000 |
| 262739 | 2010-09-20 16:55:00 | sell |  0.1 | eurusd | 2010-09-20 18:45:00 | -0.91      |         0.10000 |          0.90000 |
| 262822 | 2010-09-20 18:40:00 | buy  |  0.1 | eurusd | 2010-09-21 02:05:00 | -0.92      |         0.10000 |          0.10000 |
| 263801 | 2010-09-21 13:05:00 | buy  |  0.1 | eurusd | 2010-09-21 21:25:00 | -0.92      |         0.40000 |          0.10000 |

It does not seem to work properly. For instance the Cumulative Long for the first sell should be 0.

I'd really appreciate some help! Thanks.

Edit: 1 more question: How can I get the value for ABS(Cumulative Long-Cumulative Short)? I need the NET cumulative position size.

SELECT 
    A.*
  , SUM(CASE WHEN B.Type = 'buy'  THEN B.Size ELSE 0 END)
    AS `Cumulative Long`
  , SUM(CASE WHEN B.Type = 'sell' THEN B.Size ELSE 0 END) 
    AS `Cumulative Short`
FROM
  orders.eurusd_fx AS A
    JOIN
  orders.eurusd_fx AS B
      ON  B.`Open Time` <= A.`Open Time` 
      AND A.`Open Time` <= B.`Close Time` 
GROUP BY
    A.Ticket
ORDER BY
    A.`Open Time`
  , A.Ticket ;

or

SELECT 
    A.*
  , ( SELECT COALESCE(SUM(B.Size),0)
      FROM orders.eurusd_fx AS B
      WHERE B.`Open Time` <= A.`Open Time` 
        AND A.`Open Time` <= B.`Close Time` 
        AND B.Type = 'buy' 
    ) AS `Cumulative Long`
  , ( SELECT COALESCE(SUM(C.Size),0)
      FROM orders.eurusd_fx AS C
      WHERE C.`Open Time` <= A.`Open Time` 
        AND A.`Open Time` <= C.`Close Time` 
        AND C.Type = 'sell' 
    ) AS `Cumulative Short`
FROM
  orders.eurusd_fx AS A
ORDER BY
    A.`Open Time`
  , A.Ticket ;

To get that extra calculation, you either add another calulated column or add an extra layer (pushing the ORDER BY to that exterior layer):

SELECT tmp.*
     , ABS(`Cumulative Long` - `Cumulative Short`) AS NetCumulativeSize 
FROM
  ( SELECT 
        A.*
      , SUM(CASE WHEN B.Type = 'buy'  THEN B.Size ELSE 0 END)
        AS `Cumulative Long`
      , SUM(CASE WHEN B.Type = 'sell' THEN B.Size ELSE 0 END) 
        AS `Cumulative Short`
    FROM
      orders.eurusd_fx AS A
        JOIN
      orders.eurusd_fx AS B
          ON  B.`Open Time` <= A.`Open Time` 
          AND A.`Open Time` <= B.`Close Time` 
    GROUP BY
        A.Ticket
  ) AS tmp
ORDER BY
    tmp.`Open Time`
  , tmp.Ticket ;

This is how I understand your requirement. If wrong please clarify.

SELECT 
    A.Ticket AS Ticket,
    SUM(B.`Size`) AS `Cumulative Long`
FROM
    `orders`.eurusd_fx AS A
JOIN `orders`.eurusd_fx AS B ON (
    B.`Close Time` >= A.`Open Time`
    AND B.`Open Time` <= A.`Open Time`
    AND A.Type = B.Type
    AND A.Type = 'buy'
)
GROUP BY
    A.`Ticket`
ORDER BY
    A.`Ticket`;

I inserted my test data with different ticket numbers, by that won't change the procedure.

SELECT DISTINCT A.*, CumLong.`Cumulative Long`, CumShort.`Cumulative Short` 

FROM `orders`.eurusd_fx AS A

LEFT JOIN Cumlong ON A.Ticket = CumLong.Ticket

LEFT JOIN cumshort ON A.Ticket = Cumshort.Ticket

GROUP BY A.`Open Time` 

results in:

ticket open time   close time               type size Cumulative Long    Cumulative Short

1   2010-09-09 20:40:00  2010-09-09 20:41:00 sell 0.1 NULL               0.100000001490116

2   2010-09-13 19:30:00  2010-09-14 04:30:00 buy  0.1 0.100000001490116  NULL

3   2010-09-15 07:00:00  2010-09-15 09:15:00 sell 0.1 NULL               0.100000001490116

4   2010-09-15 09:15:00  2010-09-15 14:50:00 buy  0.1 0.100000001490116  NULL

5   2010-09-15 09:20:00  2010-09-15 14:50:00 buy  0.1 NULL               NULL

6   2010-09-16 04:35:00  2010-09-16 18:35:00 sell 0.1 NULL               0.100000001490116

7   2010-09-17 07:05:00  2010-09-20 00:02:00 buy  0.1 0.100000001490116  NULL

That's what you want? I don't think so, as the only order open while another was open too is nr 258203. Nr 258197 closes at the same time as 258203 but opens before (I understand that in case of overlap in time you want the one thar starts earlier)

So I guess that the outcome should be one line with order 258203 with a short of 0,.2?