|
|
|
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: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
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: 2 days ago @ 3:36:10 AM
Posts: 20,
Visits: 67
|
|
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
|
|
|
|