How to find history position based on date

[republiccommando]

Hello!

I need help in creating a formula that would find employee positions as of a certain date. Here is a sample layout of the position table:

Emp| Position | EffectDate
1 Intern 01/03/1997
1 Office Clerk 06/15/1999
1 Supervisor 11/12/2004
2 Manager 07/01/2003
2 Director 03/23/2007

Now, I have an audit request to provide their position at a certain point in time.

Emp| PositionAsOf| Position
1 07/01/1998 ________
1 08/01/2003 ________
2 09/15/2004 ________
2 07/06/2006 ________

Any help to point me to the right direction is greatly appreciated. Thank you.

Fact: CR9

 

[lbass]

Are the "Position As Of" values above the actual values you want to compare to? Or would you be adding a parameter for the As of Dates?

-LB

 

[PatriciaObreja]

I could do this only at database level, or in CR using a command.

If you have 2 tables in the database:

Positions
ID          Position                                           EffectDate                                             
----------- -------------------------------------------------- 
------------------------------------------------------ 
1           Intern                                             1997-01-03 00:00:00.000
1           Office Clerk                                       1999-06-15 00:00:00.000
1           Supervisor                                         2004-11-12 00:00:00.000
2           Manager                                            2003-07-01 00:00:00.000
2           Director                                           2007-03-23 00:00:00.000

And another one:

PositionAsOf
ID                   PositionAsOf                                           
-------------------- ------------------------------------------------------ 
1                    1998-07-01 00:00:00.000
1                    2003-08-01 00:00:00.000
2                    2004-09-15 00:00:00.000
2                    2006-07-06 00:00:00.000

This query:

select PositionAsOf.*,  
(
(select Position from Positions
 where PositionAsOf.ID = Positions.ID
 and PositionAsOf.PositionAsOf >= Positions.EffectDate
 and EffectDate=(select Max(EffectDate) from Positions
 where PositionAsOf.ID = Positions.ID
 and PositionAsOf.PositionAsOf >= Positions.EffectDate))
)
from PositionAsOf

Will return the required results:

ID                   PositionAsOf                                                                                              
-------------------- ------------------------------------------------------ 
-------------------------------------------------- 
1                    1998-07-01 00:00:00.000                                Intern
1                    2003-08-01 00:00:00.000                                Office Clerk
2                    2004-09-15 00:00:00.000                                Manager
2                    2006-07-06 00:00:00.000                                Manager

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[republiccommando]

lbass,

For now, they are actual values. But, later on it could be a parameter so it could report on all employee positions as of a certain date. Thanks.