Finding the tenure of an employee

Question

Post reply

Finding the tenure of an employee

S-322532

SSCommitted

Points: 1613
More actions
March 8, 2011 at 3:11 am

#233024

Hi,
I need to find out the tenure of each of the person in a table. Is is possible to do it using CTE's
create table Tenure
(
sno int identity(1,1),
EName varchar(20),
DOJ datetime default getdate()
)
insert into Tenure(ename) values ('a')
insert into Tenure(ename) values ('4')
insert into Tenure(ename) values ('x')
insert into Tenure(ename) values ('22')
update tenure set doj = dateadd(yy,-3,doj) where sno = 1
update tenure set doj = dateadd(yy,-2,doj) where sno = 2
update tenure set doj = dateadd(yy,-1,doj) where sno = 3
Can anyone help me on this 🙂
thanks
sree

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 1

Do you mean something like this?

SELECT sno,Ename,DOJ,Datediff(dd,DOJ,Getdate())

FROM tenure;

Go here for more info on datediff.

S-322532 SSCommitted Points: 1613 More actions · Answer 2

Hi rjohal-500813,

Thanks for the reply. Forget to mention one thing; the tenure of a person ends when the next person joins the office.

Check the data below:

snoENameDOJ

1axx2008-03-08 18:05:23.783

24ew2009-03-08 18:05:23.783

the tenure of employee 'axx' ends when '4ew' joins the office. So, is there a way to calculate the period of stay by 'axx' by subtracting his DOJ from that of '4ew'.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

S-322532 (3/8/2011)
Hi rjohal-500813,
Thanks for the reply. Forget to mention one thing; the tenure of a person ends when the next person joins the office.
Check the data below:
snoENameDOJ
1axx2008-03-08 18:05:23.783
24ew2009-03-08 18:05:23.783
the tenure of employee 'axx' ends when '4ew' joins the office. So, is there a way to calculate the period of stay by 'axx' by subtracting his DOJ from that of '4ew'.

Yes, it's possible. However, you have nothing in that data to link the rows by office. If you'll add that column and post your data in the very nice readily consumable format that you did in your first post, I'm sure someone will be able to help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S-322532 SSCommitted Points: 1613 More actions · Answer 4

Hi,

Thanks Jeff.

Below is the script + data

create table Tenure

(

sno int identity(1,1),

EName varchar(20),

DOJ datetime default getdate()

)

Note: there is no column avaliable mentioning date of resignation or office. So the DOJ of the successor will be considered as the last day(in office) of the predecessor.

insert into Tenure(ename) values ('a')

insert into Tenure(ename) values ('4')

insert into Tenure(ename) values ('x')

insert into Tenure(ename) values ('22')

update tenure set doj = dateadd(yy,-4,doj) where sno = 1

update tenure set doj = dateadd(yy,-3,doj) where sno = 2

update tenure set doj = dateadd(yy,-1,doj) where sno = 3

Based on the above data need to get an output similar to the attached image (sorry.. as i don't have any website nor any image album account).