Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Furkan.

Asked: January 21, 2020 - 9:19 am UTC

Last updated: January 22, 2020 - 11:20 am UTC

Version: 19.2

Viewed 50K+ times! This question is

You Asked

Hello,

I want to create table that takes id information from a different table and according to this users' start date of work, calculating the working time annually and updating it daily. How can I do it? Please help me.

I have 2 tables; employee and time. For time table, I want to take id information automatically from employee table and subtracting every employees' start date of work from employee table from system date. If result = 1 year plus, result will be automatically added to the time table.

employee.hire_date date
time.year number

I want it to do this (time.year = sysdate - employee.hire_date) process automatically.

For example, the employee started on 22.01.2019. Today(22.01.2020) write '1' on time table year column automatically.

Thanks.

and Chris said...

If you want to find employees that have hit their work anniversary, create a (scheduler) job that runs every day.

In this, search for employees who are a whole year from their start date. Here's one way do find these:

create table employees as
  select * from hr.employees;
  
update employees
set    hire_date = add_months ( trunc ( sysdate ), -12 ) 
where  employee_id = 100;

update employees
set    hire_date = add_months ( trunc ( sysdate ), -24 ) 
where  employee_id = 101;
  
create or replace view employee_hire_length as 
  select employee_id, hire_date, 
         round ( 
           months_between ( sysdate, hire_date ) / 12 
         ) employment_years
  from   employees
  where  months_between ( sysdate, hire_date ) / 12 = 
    round ( 
      months_between ( sysdate, hire_date ) / 12 
    )  ;

select * from employee_hire_length;

EMPLOYEE_ID HIRE_DATE    EMPLOYMENT_YEARS   
        100 22-JAN-2019                 1 
        101 22-JAN-2018                 2 


For details on how to turn this into a job, read:

https://oracle-base.com/articles/10g/scheduler-10g

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database