Oct 112014
 

SQL Query to Calculate Tenure Period from Date of Joining as following:

Employees with tenure of less than 30 days should be shown as “0 to 30 days”.

Employees with tenure in between 31-60 days should be shown as “31 to 60 days”.

Employees with tenure in between 61-90 days should be shown as “61 to 90 days”.

Employees with tenure in between 91-120 days should be shown as “91 to 120 days”.

Employees with tenure in between 121-180 days should be shown as “121 to 180 days”.

Employees with tenure in between 181-270 days should be shown as “181 to 270 days”.

Employees with tenure in between 271-365 days should be shown as “271 to 365 days”.

Employees with tenure of greater than 365 days should be shown as “Greater than 365 days”.

In order to calculate Tenure of Employees follow following steps:

Step 1: Create an example table “Employee” with 3 column EmpNo, EmpName and DOJ-Date of Joining as shown below:

create table employee (empno number, empname varchar2(100), doj date);

Step 2: Insert few records into “Employee” table as below:

insert into employee values (101, ‘Monty’, ’20-Nov-2012′);

insert into employee values (102, ‘Binny’, ’01-Jan-2014′);

insert into employee values (103, ‘John’, ’05-Mar-2014′);

insert into employee values (104, ‘Daniel’, ’01-Jun-2014′);

insert into employee values (105, ‘Karan’, ’21-Jul-2014′);

insert into employee values (106, ‘Sunny’, ’14-Sep-2014′);

insert into employee values (107, ‘Danny’, ’21-Aug-2014′);

Commit;

Step 3: Now write the below query for the desired result:

Here [Sysdate] is today’s date (11-Oct-2014) when I am writing this post.

select empno,empname,to_date(sysdate,’DD-MON-YYY’) Today_date,doj,round(sysdate-doj) “TENURE” ,

case when to_date(sysdate)- doj<=30 then ‘0 to 30 days’

when to_date(sysdate)- doj between 31 and 60 then ’31 to 60 days’

when to_date(sysdate)- doj between 61 and 90 then ’61 to 90 days’

when to_date(sysdate)- doj between 91 and 120 then ’91 to 120 days’

when to_date(sysdate)- doj between 121 and 180 then ‘121 to 180 days’

when to_date(sysdate)- doj between 181 and 270 then ‘181 to 270 days’

when to_date(sysdate)- doj between 271 and 365 then ‘271 to 365 days’

else ‘Greater than 365 days’ end “TENURE PERIOD”

from

employee order by empno; 

(OR)

select empno,empname,to_date(sysdate,’DD-MON-YYY’) Today_date,doj,round(sysdate-doj) “TENURE PERIOD”,

case when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj<=30 then ‘0 to 30 days’

when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj between 31 and 60 then ’31 to 60 days’

when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj between 61 and 90 then ’61 to 90 days’

when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj between 91 and 120 then ’91 to 120 days’

when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj between 121 and 180 then ‘121 to 180 days’

when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj between 181 and 270 then ‘181 to 270 days’

when to_date(’11-Oct-2014′,’dd-mon-yyyy’)- doj between 271 and 365 then ‘271 to 365 days’

else ‘Greater than 365 days’ end “TENURE”

from

employee order by empno;

Desired Output:

EMPNO EMPNAME TODAY_DATE DOJ TENURE PERIOD TENURE
101 Monty 10/11/2014 11/20/2012 691 Greater than 365 days
102 Binny 10/11/2014 1/1/2014 284 271 to 365 days
103 John 10/11/2014 3/5/2014 221 181 to 270 days
104 Daniel 10/11/2014 6/1/2014 133 121 to 180 days
105 Karan 10/11/2014 7/21/2014 83 61 to 90 days
106 Sunny 10/11/2014 9/14/2014 28 0 to 30 days
107 Danny 10/11/2014 8/21/2014 52 31 to 60 days

 

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)