Average Tenure

Question

Average Tenure

Trybbe

Hall of Fame

Points: 3967
More actions
August 18, 2008 at 7:46 am

#195916

Hi! I need help with the calculation of average employee tenure. I have something like this:-
SELECT start_dates.Effectivedatepayroll, headcountdec.StaffNo, headcountdec.CallName, headcountdec.Surname,
DATEDIFF(yy, CONVERT(datetime, start_dates.Effectivedatepayroll), GETDATE()) AS Tenure
FROM start_dates INNER JOIN
headcountdec ON start_dates.staffno = headcountdec.StaffNo
Where Headcountdec.Period = 200807
with this I get the number of years a person has worked for the company but what I actually need is is average tenure.
Please Help

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What is the grouping for which you wish to return average tenure?

It looks your query is returning per-person data, do you need average tenure in addition to this data or instead of this data? For the instead of you could do this:

[font="Courier New"]SELECT

AVG(DATEDIFF(YY, CONVERT(DATETIME, START_DATES.EFFECTIVEDATEPAYROLL), GETDATE())) AS AVG_TENURE

FROM

START_DATES INNER JOIN

HEADCOUNTDEC ON

START_DATES.STAFFNO = HEADCOUNTDEC.STAFFNO

WHERE

HEADCOUNTDEC.PERIOD = 200807 [/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

Thanks a lot.

That was helpfull, I was looking for the average tenure for each branch not individuals.

Thanks again

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Well, which column is the branch? Just modify my query to add the branch to the output and then add a group by branch.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Trybbe Hall of Fame Points: 3967 More actions · Answer 4

Trybbe

Hall of Fame

Points: 3967

August 18, 2008 at 8:15 am

#859473

I did and its working fine.

Thanks