MYSQL find corresponding value in a range of values -


i have problem how find corresponding value ...

i have table structure this:

(column-names -> column_headers (except first one) contains "range")

value |  value_level_1  |    value_level_2  |  ...  |  value_level_n  |  

so table looks like:

value |  0  |  10  |  20  |  50  |  100  |  500  |  1000  |   final_value    | ------------------------------------------------------------------------------ 15    |  1  |  0   |  3   |  5   |  0    |  0    |   0    |  = 15 * (1/100)  |     246   |  2  |  0   |  4   |  0   |  6    |  8    |   0    |  = 246 * (6/100) | 

etc ...

in first column, value (for example price) , others % value .. commission schema ..

what need do, find highest range (by value), value achieved .. , grab it's percentage value count final column (final_value) ... formulas in example above...

to make difficult :) first row, should 3rd column (10), because hights value achieved (to achieve 20 needs increased 5) .. sql should take value (=0%) 3rd colum (10)... !!! if grabbed value zero, have take previous value, until not 0 ... take second column (0) ....

i sql (dynamic sql, procedure, .... no problem)

i don't know how ... have ideas guys?

thank

peter


edit:

source table above (it calculated dynamic sql query)

company_id | value_level  |  commission_value (%)  |  product_id  | -------------------------------------------------------------------     1      |     0        |        2               |     15       |     1      |     0        |        6               |     16       |     1      |     0        |        3               |     17       |     1      |     0        |        5               |     18       |     1      |     0        |        5               |     19       |     1      |     0        |        9               |     10       |     1      |     0        |        4               |      9       |     1      |     0        |        5               |      8       |     1      |    10        |        7               |     15       |     1      |    10        |        7               |     16       |     1      |    10        |        8               |     17       |     1      |    10        |        8               |     18       |     1      |    10        |        8               |     19       |     1      |    10        |        9               |     10       |     1      |    10        |        4               |      9       |     1      |    10        |        2               |      8       | 

etc ... there 30k rows companies, it's products , different value_level

with dynamic query create pivot table ->

company_id |   product_id  |    0     |     10    |     20    |    50    | --------------------------------------------------------------------------     1      |      15       |    2     |      7    |     0     |    0     |     1      |      16       |    6     |      7    |     0     |    0     |     1      |      17       |    3     |      8    |     0     |    0     |     1      |      18       |    5     |      8    |     0     |    0     |     1      |      19       |    5     |      8    |     0     |    0     |     1      |      10       |    9     |      9    |     0     |    0     |     1      |       9       |    4     |      4    |     0     |    0     |     1      |       8       |    5     |      2    |     0     |    0     |     2      |      21       |    2     |      0    |     0     |    5     |     2      |      22       |    2     |      0    |     0     |    6     |     2      |      23       |    2     |      0    |     0     |    8     |     2      |      18       |    2     |      0    |     0     |    7     |     2      |       9       |    2     |      0    |     0     |    3     |     2      |       8       |    2     |      0    |     0     |    5     |     2      |       9       |    2     |      0    |     0     |    4     | 

etc ...

and table append data sales_table ... have value need append range ... asked above


Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -