|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/18/2008 5:26:39 AM
Posts: 2,
Visits: 4
|
|
Hi,
I'm working with this table that has account numbers and event time stamps from call centre history logs. I want to be able to summarise the table to show the min and max timestamp for call (the example data shows two calls for account number 12345). Any ideas ?
Account Timestamp
12345 08:21:32 (call 1 start)
12345 08:21:35
12345 08:22:49
12345 08:23:00
12345 08:23:35 (call 1 end)
12345 12:59:32 (call 2 start)
12345 12:59:59
12345 13:02:17 (call 2 end)
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/18/2008 5:26:39 AM
Posts: 2,
Visits: 4
|
|
Forgot to say that there are other calls from other accounts in between the two calls in the example ...
Account Timestamp
12345 08:21:32 (call 1 start)
12345 08:21:35
12345 08:22:49
12345 08:23:00
12345 08:23:35 (call 1 end)
(other calls from other accounts)
12345 12:59:32 (call 2 start)
12345 12:59:59
12345 13:02:17 (call 2 end)
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
What I don't understand is, just by looking at the data how can I say that call which started at 08:21:32 ended at 08:23:35 and not at
13:02:17 ? (I know some folks who can talk that long :o )
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/31/2009 12:13:16 AM
Posts: 24,
Visits: 113
|
|
Hi,
In work in a telco, and our data has a different format to what your describe. I agree with joe, I would expect to see some identifier for the call itself. So, with that in mind if you do have a call_id and the data looks something like this:
Account Timestamp call_id
12345 08:21:32 1
12345 08:21:35 1
12345 08:22:49 1
12345 08:23:00 1
12345 08:23:35 1
12345 12:59:32 2
12345 12:59:59 2
12345 13:02:17 2
then this SQL would create a few additional columns
SELECT T0.C0 AS acct_id
, T0.C1 AS call_id
, T2.Time_stamp AS time_exact
, T0.C2 AS time_min
, T1.C2 AS time_max
, T0.C3 AS element_id
FROM (
SELECT T0."Account" AS C0
,T0."call_id" AS C1
,MIN(T0."Time_stamp") AS C2
,COUNT(*) AS C3
FROM IPSHARE."TMANNS_tmp1" T0
GROUP BY T0."Account",
T0."call_id") T0
,(
SELECT T0."Account" AS C0
,T0."call_id" AS C1
,MAX(T0."Time_stamp") AS C2
FROM IPSHARE."TMANNS_tmp1" T0
GROUP BY T0."Account",T0."call_id") T1
,IPSHARE."TMANNS_tmp1" T2
WHERE (T0.C0 = T1.C0)
AND (T0.C1 = T1.C1)
AND (T1.C0 = T2."Account")
AND (T1.C1 = T2."call_id")
;
Apologies for the nasty formatting, it was partly auto generated SQL.
The data created by this query would be in the follwing format.;
acct_id call_id time_exact time_min time_max element_count
12345 1 08:21:32 08:21:32 08:23:35 5
12345 1 08:21:35 08:21:32 08:23:35 5
12345 1 08:22:49 08:21:32 08:23:35 5
12345 1 08:23:00 08:21:32 08:23:35 5
12345 1 08:23:35 08:21:32 08:23:35 5
12345 2 12:59:32 12:59:32 13:02:17 3
12345 2 12:59:59 12:59:32 13:02:17 3
12345 2 13:02:17 12:59:32 13:02:17 3
If you don't have a call_id or some way to identify the start of one call and the end of another, then I reckon you are screwed.
Enjoy, cheers!
Tim
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/19/2009 6:44:26 PM
Posts: 38,
Visits: 409
|
|
Like joe and Tim said we definitely need a Flag for Start and end of the calls. If we have one like Call_id then below query can be used to acheive the same.
select
Account_Num,
Call_Timestamp,
call_id
from
EDWST1E_WORK_IN.call_summary
with min( Call_Timestamp) by call_id (Title 'Minimum call Duration')
with max( Call_Timestamp) by call_id (Title 'Maximum call Duration');
NOTE:- WITH BY will not work in ODBC clients like QueryMan but will work in BTEQ.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/19/2009 6:44:26 PM
Posts: 38,
Visits: 409
|
|
The answer to the query
select
Account_Num,
Call_Timestamp,
call_id
from
EDWST1E_WORK_IN.call_summary
with min( Call_Timestamp) by call_id (Title 'Minimum call Duration')
with max( Call_Timestamp) by call_id (Title 'Maximum call Duration');
will look like
Account_Num Call_Timestamp call_id
----------- -------------- -----------
12345 08:22:49 1
12345 08:23:35 1
12345 08:23:00 1
12345 08:21:35 1
12345 08:21:32 1
--------------
Minimum call 08:21:32
Duration
--------------
Maximum call 08:23:35
Duration
12345 12:59:59 2
12345 12:59:32 2
12345 13:02:17 2
--------------
Minimum Call 12:59:32
Duration
--------------
Maximum Call 13:02:17
Duration
|
|
|
|