Hi I am trying to trying to group various time stamps into intervals this is the sort of data that i have
Interv Table
| IntervalStart | IntervalEnd | interval time |
07:00
| 07:30 | 07:00 - 07:30 |
| 07:30 | 08:00 | 07:30 - 08:00 |
| 08:00 | 08:30 | 08:00 - 08:30 |
| 08:30 | 09:00 | 08:30 - 09:00 |
| 09:00 | 09:30 | 09:00 - 09:30 |
| 09:30 | 10:00 | 09:30 - 10:00 |
| 10:00 | 10:30 | 10:00 - 10:30 |
| 10:30 | 11:00 | 10:30 - 11:00 |
| 11:00 | 11:30 | 11:00 - 11:30 |
Dialer Table
| acc_code | callednumber | campaigndescription | endstate_description | numbertype | starttime | STime |
| 716458 | 539282201 | BLO_FNB_COLL_01 - 168 | Agent Answered | Work | 2010/08/02 14:00 | 14:00 |
| 716458 | 766679811 | BLO_FNB_COLL_01 - 168 | Ringing | Cell | 2010/08/02 13:59 | 13:59 |
| 671278 | 785655645 | BLO_FNB_COLL_01 - 168 | Ringing | Cell | 2010/08/02 12:42 | 12:42 |
| 671278 | 785655645 | BLO_FNB_COLL_01 - 168 | Ringing | Cell | 2010/08/02 14:53 | 14:53 |
| 704628 | 794945247 | BLO_FNB_COLL_01 - 168 | Ringing | Cell | 2010/08/02 12:30 | 12:30 |
| 704628 | 794945247 | BLO_FNB_COLL_01 - 168 | Ringing | Cell | 2010/08/02 14:40 | 14:40 |
I want to track the volume of calls per endstate, per interval, on a daily ,monthly,on a campaign level etc....
This is my script but i am not sure if the correct way to do it.
Any help will be appreciated.
Interv:
LOAD
TIME(IntervalStart,'hh:ss')as "IStart",
TIME(IntervalEnd,'hh:ss')as "IEnd",
[interval time]as "IntervalTime"
FROM
(ooxml, embedded labels, table is Sheet1);
Dialer:
LOAD acc_code,
callednumber,
campaigndescription,
endstate_description,
numbertype,
starttime,
TIME(STime,'hh:ss')as "Mtime"
FROM
(ooxml, embedded labels, table is Sheet1);
IntervalMatch ( Mtime) LOAD IStart,IEnd RESIDENT Interv;
Thanks
Shaun
