Mysql query to join two tables and calculate opening and closing amount by date

See more:

MySQL

I have two MySQL data tables.

RECEIVED-IN TABLE:

IN_ID  | date | product |  I  |  II  |  T   |<br />
---------------------------------------------<br />
1      |1.2.16| GG20206 | 200 |  10  |  12  |<br />
2      |1.2.16| KK20206 | 300 |   5  |  20  |<br />
3      |2.2.16| GG20206 | 150 |  50  |  25  |<br />
4      |3.2.16| KK20206 |  50 |  20  |  30  |<br />
5      |5.2.16| TT20206 |  50 |  20  |  30  |

By using SQL query

SELECT date, RIGHT(product, CHAR_LENGTH(product) - 1) AS PRD, SUM(I) AS InI, SUM(II) AS InII, SUM(T) AS InT FROM received_in GROUP BY PRD,date

I get the table as:

Table 2

| date |   PRD   | InI | InII | InT  |<br />
--------------------------------------<br />
|1.2.16|  G20206 | 500 |  15  |  32  |<br />
|2.2.16|  G20206 | 150 |  50  |  25  |<br />
|3.2.16|  K20206 |  50 |  20  |  30  |<br />
|5.2.16|  T20206 |  55 |  23  |  45  |

The RECEIVED-OUT TABLE is as follows:

OUT_ID | date | product | ex  | qty  |<br />
--------------------------------------<br />
1      |1.2.16|  G20206 |  I  |  30  |<br />
2      |1.2.16|  G20206 |  I  |  75  |<br />
3      |1.2.16|  G20206 | II  |  90  |<br />
4      |3.2.16|  K20206 |  T  | 100  |<br />
5      |4.2.16|  T20206 |  T  |  25  |

Again by using SQL query

SELECT date, product AS PRD, SUM(IF(ex = 'I', qty, 0)) AS OI, SUM(IF(ex = 'II';, qty, 0)) AS OII, SUM(IF(ex = 'T', qty, 0)) AS OT FROM received_out GROUP BY product,date

I get the Table 4 as:

Table 4

| date |    PRD  | OI  |  OII |  OT  |<br />
--------------------------------------<br />
|1.2.16|  G20206 | 105 |  90  |   0  |<br />
|3.2.16|  K20206 |   0 |   0  | 100  |<br />
|4.2.16|  T20206 |   0 |   0  |  25  |

Now I try to join(INNER JOIN?) Table 2 and Table 4 and do some calculations and output the SQL query like the following table:

Final Table

| date |   PRD  | OpI  |  OpII |  OpT  | InI | InII | InT  |  OI  |  OII |  OT  | ClI  |  ClII |  ClT  |<br />
--------------------------------------------------------------------------------------------------------<br />
|1.2.16| G20206 |  0   |   0   |   0   | 500 |  15  |  32  | 105  |  90  |   0  | 395  |  -75  |  32   |<br />
|2.2.16| G20206 | 395  |  -75  |  32   | 150 |  50  |  25  |  0   |   0  |   0  | 545  |  -25  |  57   |    <br />
|3.2.16| K20206 |  0   |   0   |   0   |  50 |  20  |  30  |  0   |   0  | 100  |  50  |   20  | -70   |<br />
|4.2.16| T20206 |  0   |   0   |   0   |   0 |   0  |   0  |  0   |   0  |  25  |   0  |    0  | -25   |  <br />
|5.2.16| T20206 |  0   |   0   | -25   |  55 |  23  |  45  |  0   |   0  |   0  |  55  |   23  |  20   |

ClI = (OpI+InI) - OI
ClII = (OpII+InII) - OII
ClT = (OpT+InT) - OT

And OpI is the previous entry of ClI for a particular PRD
In the same way, OpII is the previous entry of ClII and OpT is the previous entry of ClT for a particular PRD.

I don't understand how to write the SQL query to output the Final Table. Any help/guidance will be appriciated. Thanks in advance.

What I have tried:

I tried INNER JOIN but I don't understand how to do the calculations.