Hi
Why dont you try with time characteristics '0CALWEEK'. Add '0CALWEEK' to your infocube & map it with the date which you already have in the table. Now consider the your example for employee '100' -
100 6/8/2009 8
100 6/9/2009 8
100 6/10/2009 8
100 6/11/2009 8
100 6/12/2009 8
by adding Calweek you will get - 100 24.2009 (that is 24 th week) 40 hrs.
Now in your query divide this 40 hrs (that is total working hours in week) with daily min working hrs. (In your case assume it 8 Hrs per day). So in query you will get number of working days in a week for each employee. From this you can easily find out how many leaves each employee has taken.
I think this is what you are looking for.
Regards
Sandeep
Does that solution deal with weekends? What about holidays? If you are calculating number of days off consecutive within a week you are missing those I believe.
Even if you skip holdays, which are a huge problem with calculating consecutive days off, then weekends themselves should be considered.
If you used 0CALWEEK would this senario be captured as 5 consecutive days off?
Week 12 - M - 8 hrs worked
Week 12 - T - 8 hrs worked
Week 12 - W - 8 hrs vacation
Week 12 - H - 8 hrs vacation
Week 12 - F - 8 hrs vacation
Week 13 - M - 8 hrs vacation
Week 13 - T - 8 hrs vacation
Week 13 - W - 8 hrs worked
Week 13 - H - 8 hrs worked
Week 13 - F - 8 hrs worked
Add comment