Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 - 
         round((months_between(sysdate,e.hire_date))/12)))
from employees e
share|improve this question

1 Answer 1

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

testbed:

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'));

query:

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;

result:

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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