Calculate Percentage help

Question

Hi this code gives me employee salaries and manager salaries.

SELECT E.EMP_FNAME AS MANAGER, E.EMP_SALARY, D.DEPT_NO, A.EMP_FNAME AS EMPLOYEE, A.EMP_SALARY
 FROM EMPLOYEE E, EMPLOYEE A, DEPARTMENT D
 WHERE E.EMP_NIN = A.EMP_MANAGER
 AND A.EMP_MANAGER = D.EMP_MANAGER;

How can i only show the employees that have a salary within 10% of their manager salary?

Answer 1

Assuming you want to allow for employees earning more than their managers (you know it would happen, in a different, better world) just include this in the WHERE clause:

AND  A.SALARY BETWEEN (E.SALARY * 0.9) and (E.SALARY * 1.1)

edit

This uses simple mathematics. 0.9 = 90% and 1.1 = 110%; this line restricts the resultset to EMPLOYEE records where the SALARY is with +/- 10% of their manager's SALARY. If you are certain that employees can never earn more than their manager then you need a simpler greater than test...

AND  A.SALARY >= (E.SALARY * 0.9)