|
Hello everyone,
i try to extract some data from two tables : call, activity.
many activities can be done on one call under a status, so i try to pull out how much time was spend in an activity under a certain status.
call table : *id_call, receved_date, closed_date.
activity table : *id_activity, begin_date, begin_time, end_date, end_time, stauts, #id_call
i will make an example :
call 1 : id_call=B21, receved_date=02/02/2009,closed_date=05/02/2009
activity1 : id_activity=02ZE, begin_date=02/02/2009, begin_time=11:10,end_date=02/02/2009, end_time=11:30, status=open, id_call=B21.
activity2 : id_activity=03ZE, begin_date=02/02/2009, begin_time=12:10,end_date=02/02/2009, end_time=12:30, status=open, id_call=B21.
activity3 : id_activity=02ZE, begin_date=02/02/2009, begin_time=14:10,end_date=02/02/2009, end_time=14:30, status=open, id_call=B21.
i have already made a report which pull all the activities of each call grouped by status, so what i want to do is calculating the duration from the first activity untill the last under the status "Open".
i have the function which takes care of calculating the duration between two dates, i just need to know how can i pull out the first date and the last date of each group and give it to the function so it calculates the duration ???
in my case i need to pull out:
begin_date=02/02/2009, begin_time=11:10
And
end_date=02/02/2009, end_time=14:30.
thank you for your help.
Edited by dimmu - 27 Jan 2009 at 2:24pm
|
I am guessing that the data is already grouped by the call and ordered by the begintime. I am assuming that the begin time cannot overlap the endtime.
Here, let's just say that records are grouped by call, no specific ordering. I would have 3 formulae, 1 to reset the value to 0, 1 to display/calculate results, and 1 to do all the work. By all the work, it would be on the detail line, it would ask if the begin_time was less than the stored value, if yes, update, othewise nothing. Is the end_time greater than the stored value, if so update otherwise nothing. In the group footer you could now select the begin and end times of the call.
Hope this helps
|
IP Logged
