CODEWITHSUNDEEP
oracle-databasedate-arithmetic
Don Vado
Don Vado

Reputation: 1

How to find employees hired less than ten years ago?

I want to write a query to display the employee number, last name, hire date, and number of months employed for all employees who have worked for less than 10 years.

This is what I have but something is wrong with the WHERE function:

SELECT employee_id, last_name, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) "number of months employed" 
FROM employees
WHERE MONTHS_BETWEEN(SYSDATE, hire_date) <= 120;

PS: when I run it without the WHERE function, for the "number of months employed" I get 292.530207959976105137395459976105137395

please help! :(

Upvotes: 0

Views: 12230

Answers (1)

APC
APC

Reputation: 146349

"when i have the "WHERE MONTHS_BETWEEN(SYSDATE, hire_date) <= 120;" added and run it, i will get no data found "

This means the company hasn't hired any employees in the last ten years.

"and when i remove the 'WHERE MONTHS_BETWEEN(SYSDATE, hire_date) <= 120;` i will get the results of ALL employees in the table and the "number of months employed", would give me 292.530207959976105137395459976105137395. "

You are using a table based on the cannonical SCOTT.EMP table, where all the employees were hired at some point in the 1980s.

"Now, want to find the persons who were working there less then 10 years."

You'll need to amend your data. Try something like this query. It will lop approx twenty five years off the hiredate of any employee who ID ends in an odd digit:

update employees
set hiredate = add_months(hiredate, 300)
where mod(employee_id,2)=1
/

Upvotes: 3

Related Questions