For a University course I've been given the following task

Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every employee each year after he/she is hired, must renew his contract with company. Show how many months remain before the next renewal for each employee.

This is my attempt:

select (abs(months_between(sysdate,hire_date)/12 - 
from employees e

There are more efficient ways of doing this if your employee table is large, but this is the way I find easiest to understand :)


create table employee( employee_id integer primary key, 
                       name varchar(100) not null, 
                       hire_date date not null );

insert into employee(employee_id, name, hire_date)
values(1, 'Alice', to_date('20090909', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(2, 'Bob', to_date('20101010', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(3, 'Chris', to_date('20111111', 'YYYYMMDD'));

insert into employee(employee_id, name, hire_date)
values(4, 'David', to_date('20101231', 'YYYYMMDD'));


with w as ( select e.*, ( select max(add_months(hire_date, 12*level))
                          from dual
                          connect by add_months(hire_date, 12*(level-1))<sysdate )
                      as next_anniversary
            from employee e )
select w.*, round(months_between(next_anniversary, sysdate)) as months_from_now from w;


EMPLOYEE_ID            NAME       HIRE_DATE                 NEXT_ANNIVERSARY          MONTHS_FROM_NOW
---------------------- ---------- ------------------------- ------------------------- ----------------------
1                      Alice      09-SEP-09 00.00.00        09-SEP-12 00.00.00        10                     
2                      Bob        10-OCT-10 00.00.00        10-OCT-12 00.00.00        11                     
3                      Chris      11-NOV-11 00.00.00        11-NOV-12 00.00.00        12                     
4                      David      31-DEC-10 00.00.00        31-DEC-11 00.00.00        1                      

You may prefer floor or ceil to round.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.