Calculating tenure in position

Question

Post reply

Calculating tenure in position

Trybbe

Hall of Fame

Points: 3967
More actions
February 26, 2010 at 2:32 am

#217272

Hi there
Please help
my client would like a list of employees showing how long an employee has been in their current position/job.
I don't have an idea of where to start with the query.

Viewing 7 posts - 1 through 6 (of 6 total)

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

vaibhav.tiwari SSCommitted Points: 1946 More actions · Answer 1

Please post table structure for dates

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Go to Books Online (Books online is the documentation/help available with SQL Server), look for DATEDIFF function.

Partial Syntax of the function is:

DATEDIFF( Interval, Date1, Date2 )

--Ramesh

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

Sorry about that

Staffno name position posid headcount period

123 john developer d24 Newhire 200911

321 Doe Analyst A15 TransferIn 200906

NULL NULL Web W33 Vacancy 200910

So I would like to know how long each of these people has been in their current positions, was it from date of emplyment or since they transfered from different positions

LutzM SSC Guru Points: 107049 More actions · Answer 4

What would be the expected result for the (very limited) sample data?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Trybbe (2/26/2010)
Sorry about that
Staffno name position posid headcount period
123 john developer d24 Newhire 200911
321 Doe Analyst A15 TransferIn 200906
NULL NULL Web W33 Vacancy 200910
So I would like to know how long each of these people has been in their current positions, was it from date of emplyment or since they transfered from different positions

You don't have to if you don't want to but to get tested code answers really quickly, take a look at the first link in my signature line below. A lot of us won't post code unless we've tested it and we just don't have time to make your data "readily consumable" even if it's only 3 lines. 😉

--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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 6

A guess:

DECLARE @Employee

TABLE (

row_id INTEGER IDENTITY PRIMARY KEY,

staff_no INTEGER NULL,

name NVARCHAR(50) NULL,

position NVARCHAR(50) NOT NULL,

pos_id CHAR(3) NOT NULL,

headcount VARCHAR(10) NOT NULL,

period CHAR(6) NOT NULL

);

INSERT @Employee

(

staff_no,

name,

position,

pos_id,

headcount,

period

)

VALUES (

123,

N'John',

N'Developer',

'D24',

'Newhire',

'200911'

);

INSERT @Employee

(

staff_no,

name,

position,

pos_id,

headcount,

period

)

VALUES (

321,

N'Doe',

N'Analyst',

'A15',

'TransferIn',

'200906'

);

INSERT @Employee

(

staff_no,

name,

position,

pos_id,

headcount,

period

)

VALUES (

NULL,

N'Web',

'W33',

'Vacancy',

'200910'

);

SELECT staff_no,

name,

position,

pos_id,

headcount,

period,

tenure_days = DATEDIFF(DAY, CONVERT(DATETIME, period + '01', 112), CURRENT_TIMESTAMP)

FROM @Employee

WHERE staff_no IS NOT NULL

ORDER BY

name ASC;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi