Hi,
I need to create a set analysis which retrun the following:
I have 2 tables:
The first is employees: each employee is related to one director and has a date in which he joined the company
employee | director | date_joined
emp_1 | dir_1 | 01/02/2013
emp_2 | dir_3 | 01/03/2013
emp_3 | dir_1 | 02/04/2006
Teh second is calls: each employee can make few calls and each call has a date and a status:
call | employee | call_date | call_status
call_1 emp_1 01/05/2013 ok
call_2 emp_1 06/05/2013 ok
call_3 emp_1 03/05/2013 failed
call_4 emp_3 01/06/2013 ok
I would like to have for each director the average for all his employees of the substraction of employee date joined and the first call with status ok.
I have tried the following, which doesn't work:
aggr (avg (aggr (min(call_date <call_status= ok>), employee) - date_joined),director).
aggr (min(call_date <call_status= ok>), employee) - to have for each employee the first call with status = ok.
Now, I need to calculate the following substraction :employee date_joined - employee first call ( calculated in the previous line)
aggr (min(call_date <call_status= ok>), employee) - date_joined (??)
And to have the average pf this substraction( number of days) per director:
aggr (avg (aggr (min(call_date <call_status= ok>), employee) - date_joined),director).
I would really aprreciate your help.
