[Gabukun Contributions: 0Level 1☆]

Hi everybody,

In order to track employee turnover in our company, I'm trying to configure a saved search picking up the {hiredate} and {releasedate}, group them by calendar month, and sum up the respective joining or leaving employees' availability {custentity_emp_availability} as a positive amount when joining and a negative amount when leaving respectively. This is all pretty simple if I hardcoded the months into the individual columns. In that case, though, I would need to edit the saved search every now and then.

In order to make it dynamic and have the calendar month range extended, regardless of whether HR adds a new employee record with hire or release date two years into the future or five years in the past, I am facing the following two challenges.

1) Is there any calendar I can outer join the employee records to? I was thinking of the work calendar, but that one lists weekdays. Is there any other calendar I could call on and if not, what else would you advise?

2) How do I write the SQL expression to render employees' availability positive or negative depending on whether for the employee has his hire or release date in the respective calendar month? The logic goes that in the row 2016-01, fulltime employees with a hire date in January 2016 are added +1, meanwhile fulltime employees leaving that month are substracted as -1. In case of a hard coded calendar, I would simply write CASE WHEN TO_DATE({hiredate}, 'MM/YYYY') = '01/2016' THEN 1 WHEN TO_DATE({releasedate}, 'MM/YYYY') = '01/2016' THEN -1 ELSE 0 END.

I'm really looking forward to your help on this one.
This is a cached copy. Click here to see the original post.

[Author]

Posts