Intersecting column and row in a table according values in other table and further calculations

Question

I’m a newbie in SQL and I tried to do what I need following some tutorials and other questions here, but I got mostly a mess. Therefore, I will much appreciate any help with this: I have two tables that I labelled as payoffTable and as inTable (see below and here). The inTable will be filled by players (I still don’t know how I’ll link players and what they do with the database. To keep this question simple I won’t go through that for now).

Players will fill in the column “contributing?" with values 1 (positive) or 0 (negative). If players fill 0, then they must fill in the column “quota_EZ” with the value 0 and “quota_NEZ” with the value 3. If they fill in with the value 1, then they are allowed to fill in the column “quota_EZ” with 0, 1, 2 or 3 and the “quota_NEZ” with the same values, but the sum of rows of both columns must be 3. I’m giving this information just to allow you to understand the table. Again, to keep it simple, for now I don’t want to set any control in case any user inappropriately fills the table.

Here are the tables showing what I just exposed.

inTable, the values in the first 3 column are an example of values that players would fill in (players will fill in the table during 12 independent rounds, so I should store the outputs of every round, but for now I prefer not going through this to keep it simple, unless it is easy to do and you're keen to show me how to do that).

With the sum of values in the column “contributing” I need to select one of the columns between EZ_0 and EZ_8 in the payoff table. At the same time with the sum of column “quota_EZ” I need to select a row in the payoff table (between 0 and 24, column “quotas”). I need the value in the intersection of the selected column and selected row for further calculations. With the sum of the “quotas_NEZ” I need to select a row in the payoff table through the column labelled as NEZ. Also I need the value of this intersection for further calculations. I could get the values in the mentioned intersections manually setting the values of column and row, but I’ve been unable to do it linking both tables.

payoffTable

The calculation that I need to do will fill the following columns in the inTable: “revenueEZ”, “revenueNEZ”,“all_pay”, “by_use”, “not_paying” and “by_use_sharing”.

For simplicity I’ll show the calculations in excel code

revenuesEZ: =D5*$D$3
revenuesNEZ: =IF(E5>0,E5*$E$3, IF(E5=0,0,""))
all_pay: =IF(C5>0,F5-$C$3+G5,F5+G5) # note that the value in C3 is fix (=10)
by_use: =IF(C5>0,F5-$C$3+G5,"")
not_paying: =IF(C5=0,G5,"")
by_use_sharing: =+IF(C5=1,I$13/K$14, "")

Thanks in advance for any help

Answer 1

See http://sqlfiddle.com/#!6/d81fc/11/0 for a head start. You need to calculate the sums and lookups into variables and then apply these in the update calcualtions.