Code help needed to summarise table

Analytics

Code help needed to summarise table

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)

5 REPLIES

Re: Code help needed to summarise table

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)
Enthusiast

Re: Code help needed to summarise table

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 )
Enthusiast

Re: Code help needed to summarise table

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
Enthusiast

Re: Code help needed to summarise table

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.
Enthusiast

Re: Code help needed to summarise table

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