How to locate information by considering a range (range of numbers)

Asked

Viewed 189 times

0

In the company where I work we are automating the process of calculating variable remuneration, and for this we are transposing all the logic that was in the EXCEL to the database. However, I’m having some difficulty.

I have a table with information that are used as parameters to improve the employee depending on their performance.

TABELA FATOR

For example: the collaborator reached a Factor of 14.78. In this case it would be in line 4 (BASE: 12.64920 | CEILING: 25.29830), so its bonus would be 57.14%. No EXCEL to fetch the bonus percentage I would simply make a PROCV using the approximate match.

In SQL Server I am trying to do the same, but without success. I tried an INNER JOIN with BETWEEN, obviously it did not work.

Below is the table with the performance of the collaborator. From the DELIVERY column I have to go to the FACTOR table, observe the range (BASE and CEILING) and find the corresponding BONUS percentage.

inserir a descrição da imagem aqui

sql sql-server excel

  • I didn’t get it right. Shouldn’t his bonus be 57.14%? ?

  • Corrected! Should be 57.14% the bonus. Basically I have to bring the information of the BONUS column taking into account the information of BASE and CEILING (base is the beginning of the interval and ceiling is the end of the interval)

  • Puts in the sqlfiddle how to construct the tables, make some examples Insert, it helps a lot to understand the structure of your bank and to answer your question

2 answers

1


Since the track is in the same row of the table, you can use the BETWEEN to find the delivery value between base and ceiling:

select dados.nome, parametros.bonus
  from dados, parametros
 where dados.entrega between parametros.base and parametros.teto

In this example, I called the first parameter table and the second data table. I took the BETWEEN to do the JOIN tables, since they have no key that relates.

See the example working here: sqlfiddle

0

Without your charts, it’s kind of hard to answer. If you have a table exactly like this excel and a table with the contributor factor, try something like this

SELECT fat.bonus FROM tabela_fator fat,(SELECT cola.entrega FROM tabela_colaborador cola) AS entrega WHERE entrega>=fat.base AND entrega<=fat.teto

Browser other questions tagged sql sql-server excel

You are not signed in. Login or sign up in order to post.