How to track movement between departments (calculate retention)

Hello,

In order to do a retention calculation I need to track the employees from month to month.

So let's say I want to find the retention for a department. I need to know who started in that department (at the beginning of the year) and if they are still in that department (at the time of the query).

So if there were 100 employees and now there are 90, obviously the headcount is down by 10 people. However, it could have been down by 20 people but 10 more were hired back in. So 20 people left that department.

If this calculation was just a headcount one, it would be easy to calculate. But I need to track each person.

Is there a methodology that anyone is aware of that would allow me to compare the beginning group to the current group and count who has left, by employee?

This can get much worse because my leadership asks for retention by department or manager or geographic area.

Thank you for the help.

-Gumbatman

If you are tracking just counts, no measures are associated, then one of the options:

Create snapshot for initial load of employees. This could be done just once, or yearly or in some cases even monthly.

For each change create record with -1 for exit and +1 for entry. This should be done in SQL server, so your loads into SSAS are simple.

That is if employee changed department, there is record for same day key for old department with -1 as measure and then +1 for new department.

Same rule if employee changes more than 1 attribute at the same time (department + manager) : record for exit with old attributes and record for entry with new attributes.

Sum of records from start snapshot to any day will give you who is in department that day.

This should be enough to track each employee.

Lets say you have 100 employees in HR. You create 20 exits, 10 entries. At the end you have sum of 90, all accounted. Plus, you can filter by any other attribute and have correct results.

Vidas Matelis

|||

Vidas,

That is a pretty cool method, I need to work through it a little more the get my head around it.

Thank you for the help!

-Gumbatman

How to subtract previous value of one column from current value of another column

I have a table like this:

BottomTop

02

46.5

914

1517

In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.

So, the result would be 0, 2, 2.5, 1

I must return the value in a table. Thank you all for your help!

Karen

If you have a column to sequence by then you can try something similar to this:

create table test(seq int identity, bottom float, [top] float)

insert into test values(0,2)

insert into test values(4,6.5)

insert into test values(9,14)

insert into test values(15,17)

select CASE WHEN (SELECT B.[top] FROM test B WHERE B.seq = (SELECT MAX(seq) FROM test WHERE seq < A.seq)) IS NULL

THEN

A.bottom

ELSE

A.bottom - (SELECT B.[top] FROM test B WHERE B.seq = (SELECT MAX(seq) FROM test WHERE seq < A.seq))

END

from test A

order by A.seq

|||

Karen,

If you are using SQL Server 2005, you can take advantage of the

new analytic functions more or less like this:

create table T(

primary_key int identity(1,1) primary key,

bottom decimal(10,2),

[top] decimal(10,2)

)

create index T_bottom on T(bottom,primary_key) include ([top])

insert into T values (0,2)

insert into T values (4,6.5)

insert into T values (9,14)

insert into T values (15,17)

go

with Tnumbered as (

select

bottom,

[top],

row_number() over (order by bottom, primary_key) as rownum

from T

)

select

this.bottom,

this.[top],

this.bottom - coalesce(prev.[top],0) as bottom_minus_prev_top

from Tnumbered as this

left outer join Tnumbered as prev

on this.rownum = prev.rownum + 1

go

drop table T

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

mz1derful@.discussions.microsoft.com wrote:

> I have a table like this:

>

> Bottom Top

>

> 0 2

>

> 4 6.5

>

> 9 14

>

> 15 17

>

> In Transact SQL I need to calculate the difference between the current

> bottom and the previous top. If there is no previous top, then the

> result would be the current bottom.

>

> So, the result would be 0, 2, 2.5, 1

>

> I must return the value in a table. Thank you all for your help!

>

> Karen

>

>

|||

SELECT MyTop, MyBottom,
CASE WHEN MyTop = 0 OR MyBottom = 0 THEN 0 ELSE (MyBottom/MyTop) END AS [Difference]
FROM MyTestTable

Adamus

|||

NNTP User wrote:

Karen,

If you are using SQL Server 2005, you can take advantage of the

new analytic functions more or less like this:

create table T(

primary_key int identity(1,1) primary key,

bottom decimal(10,2),

[top] decimal(10,2)

)

create index T_bottom on T(bottom,primary_key) include ([top])

insert into T values (0,2)

insert into T values (4,6.5)

insert into T values (9,14)

insert into T values (15,17)

go

with Tnumbered as (

select

bottom,

[top],

row_number() over (order by bottom, primary_key) as rownum

from T

)

select

this.bottom,

this.[top],

this.bottom - coalesce(prev.[top],0) as bottom_minus_prev_top

from Tnumbered as this

left outer join Tnumbered as prev

on this.rownum = prev.rownum + 1

go

drop table T

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

mz1derful@.discussions.microsoft.com wrote:

> I have a table like this:

>

> Bottom Top

>

> 0 2

>

> 4 6.5

>

> 9 14

>

> 15 17

>

> In Transact SQL I need to calculate the difference between the current

> bottom and the previous top. If there is no previous top, then the

> result would be the current bottom.

>

> So, the result would be 0, 2, 2.5, 1

>

> I must return the value in a table. Thank you all for your help!

>

> Karen

>

>

This code works great if it's static and you already have the answer.

Other than that...Programming 101

Adamus

|||

What do you mean by this? Does the code not work? Why wouldn't the code be static? No problem saying something is wrong but please substantiate, otherwise you are just being negative.

|||

The recordset will ALWAYS be dynamic.

You have no idea what the values will be.

Adamus

|||So are you saying that Steve's code only works for those particular values?|||

Louis Davidson wrote:

So are you saying that Steve's code only works for those particular values?

That is correct. Hence, the hard coding.

You could populate the table dynamically but what would be the point of going to that extreme when a simple solution exists?

...and please don't preach performance on such a petty problem.

Adamus

|||

Louis Davidson wrote:

What do you mean by this? Does the code not work? Why wouldn't the code be static? No problem saying something is wrong but please substantiate, otherwise you are just being negative.

Just because the code works and/or might be clever doesn't mean it's the best solution.

Adamus

how to store values during dataflow

The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.

How can i get a value that was calculated before? Can i store it in a variable?

Many Thanks!

On the Control Flow add a 'Execute SQL Task' which has something like SELECT MAX(ID) AS MAXID FROM YOURTABLE, store the result into a variable.

Do your inserts, then SELECT * FROM YOURTABLE WHERE ID > YOURVARIABLE

|||

Thanks Paul,

The only way to store this results on a variable is from an 'Execute SQL Task'? can i do the same from either a OLEDB Command or Agregate Transformation?

|||You can use a script component to store values in a variable inside a data flow. You have to write it in the PostExecute method, though, which runs after all the rows pass thorugh it. However, I don't know if that is the best approach, given the scenario you are describing. But, ultimately, it's your call.|||

Thank you very much jwelch, ill try it!