DATA Step, Macro, Functions and more

How to create Staff table that picks each staffs depart based on the role date for reporting month

Reply
Occasional Contributor
Posts: 19

How to create Staff table that picks each staffs depart based on the role date for reporting month

Hi Guys,

 

I have a peoplesoft HR data (Emp_ID,Emp_Name,EMP_Status,PosnID,...Dept_ID, EFFT_D,EXPY_D)..in this table i have list of each employees details with their historiy movements..i mean if employee moves to another team his/her details changes with ,PosnID,...Dept_ID, EFFT_D,EXPY_D...

 

like below...

EMPL_IEMPL_MSRNM_MFRST_MSCND_MEMPL_STUS_CPOSN_IDEPT_IEFFT_DEXPY_D
12345XXX XXX ACT2001176500992603May199919May2002
12345XXX XXX ACT2001177100335220May200212Jan2003
12345XXX XXX ACT2001177100335213Jan200329May2003
12345XXX XXX ACT2001177101088930May200321Aug2003
12345XXX XXX ACT2001177101088922Aug200331Aug2003
12345XXX XXX ACT2002980401238101Sep200320Oct2004
12345XXX XXX ACT2002952501211321Oct200401May2005
12345XXX XXX ACT2002980401240802May200505Sep2005
12345XXX XXX ACT2002980401240806Sep200515Jun2006
12345XXX XXX ACT2002980401240816Jun200603Nov2006
12345XXX XXX TRM2002980401240804Nov200631Dec2099
23456YYY   ACT2003490701362425Feb200808Oct2008
23456YYY   ACT2004066200855409Oct200826Apr2009
23456YYY   ACT2004066200855427Apr200930Jun2010
23456YYY   ACT2003807900855401Jul201012Jun2011
23456YYY   ACT2003805400873313Jun201116Sep2012
23456YYY   ACT2000039500857317Sep201231Jul2014
23456YYY   ACT2000039500855401Aug201426Oct2014
23456YYY   ACT2013389801471727Oct201408Jun2015
23456YYY   TRM2001052200072915Aug201531Dec2099

 

My monthly report is picks only by considering the employee EXPY_D = 31DEC2099....so the  report captures the recent department for that employee...

 

Issue is :When an employee moves onto new role.the hierarchy moves with them and the credit goes to latest(new) department..

 

How can i create the dataset that can consdier the role and department based on reporting Date/Review Date?

 

Appreciate your help and response...Please let me know if its not clear...thanks

 

Regards,

RS

Occasional Contributor
Posts: 8

Re: How to create Staff table that picks each staffs depart based on the role date for reporting mon

Posted in reply to BaalaRaaji

Do you have some other dataset that is being joined to this one that lists the credits per employee per date? At the moment you've only shown the employee's hierarchy history, but that doesn't let us know exactly how the data is being used to generate the report.

 

In general you would likely be looking at joining the credits data to the entire employee history not just the latest record, and not just on the employee id matching but also the date range. That should let you pull the group for the employee for the given date.

Occasional Contributor
Posts: 19

Re: How to create Staff table that picks each staffs depart based on the role date for reporting mon

Hey,

 

thanks for quick reply.

 

Am joining with the the peoplesoft data(above listed) with the other dataset that has empl_n(employee name)

 

i have only employee name to join and can get review date to check if it falls within that dates...

 

like below,...

 

Review_DateEMPL_N
31Jan2017XXX
18Jan2017XXX
25Jan2017XXX
01Feb2017XXX
06Feb2017YYY
13Feb2017YYY
07Mar2017YYY

 

 

Later am joining to department dataset that has dept_i(dept code)

 

unfortunately i dont have any unique variables to consider? even for more than 2 employee has same name...it picks only the latest...

 

hope it is clear...thank you

 

Ask a Question
Discussion stats
  • 2 replies
  • 89 views
  • 0 likes
  • 2 in conversation