0

I have two tables with the same attributes in MySQL. I want the difference between two columns but if the difference is equal to 0 the rest value of one of the columns has to calculate for the next row.

For example:

P1, K1, 5, 2017-06-31; P2, K1, 6, 2017-05-21; for the first table and
P3, K1, 3, 2017-07-03; P4, K1, 7, 2017-06-21; for the second table.

The result must be:

P1, K1, 0 AND P2, K1, 1.

This because we have 5 for P1 and K1 that are input and 7 of P4 and K1 that are output with the FIFO logic. So 5 - 7 = 0 and the rest is 2. The next date after the first is P2 AND K1 with 6. The rest from the first was 2 so 6 - 2 = 4 so we have to look at the second date of output that is P3 and K1 with 3 value so 4 - 3 = 1. In conclusion the result is P1, K1, 0 AND P2, K1, 1.

Please help.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
atdhetari
  • 1
  • 2
    My attempt to format doesn't enlighten me. I would suggest that you provide data in a *tabular* format in the question and try to be as clear as possible about what you want to do. Prepend each row in the table with four spaces. Or, attempt a table and someone else will format it. – Gordon Linoff Sep 23 '17 at 12:28
  • I'm trying but i'm new in this and i have some problems in tabular format. Thank you – atdhetari Sep 23 '17 at 12:31
  • . . There are also basic things like column names and table names that would help anyone attempting to answer the question. – Gordon Linoff Sep 23 '17 at 13:04
  • Table names are T1 and T2 and column names are P, K, Value and date – atdhetari Sep 23 '17 at 13:34
  • So the first table is for IN and the second table is for OUT? And the first event that happened is 6 IN on 2017-05-21? And the next event was 7 OUT on 2017-06-21? That would be more OUT than IN. How can that be? I still don't get it. – Thorsten Kettner Sep 23 '17 at 14:25
  • This feels like a running total, see [here](https://stackoverflow.com/q/2563918/6248528) for an example (you would use the sum from table 2 as an offset). If I understand you correctly (which I doubt), as a result, you want basically every row, but with the amount 0 for all rows that are "used up" by the output-table? What should happen if the output is larger than the input? Also: could you explain why P1 (from Jun 2017) is taken before P2 (from May 2017)? Do you start one table from the end, one from the beginning? – Solarflare Sep 23 '17 at 14:26
  • There is no problem if the output is larger than the input. I have to calculate only the situation of the input in the moment that i execute the query. If the output is larger than the input i don't care because i need only the input situation. Solarflare you understand it correctly. Thank you very much guys – atdhetari Sep 23 '17 at 14:34
  • That's hard to do in MySQL. It's basically numbering the rows of the tables (t1 in descending order, t2 in ascending order), then join the two tables on those row numbers, and then build a running total. Rather easy with standard SQL window functions (`ROW_NUMBER(...) OVER (...), SUM(...) OVER (...)`), but MySQL doesn't feature them. – Thorsten Kettner Sep 23 '17 at 14:50

0 Answers0