Interval Match - SQL-Between Equavalent

Thu, 05 Aug 2010 14:59:16 GMT

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

topic Interval Match - SQL-Between Equavalent in QlikView

Interval Match - SQL-Between Equavalent