InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comFri, 05 Sep 2008 19:22:07 GMT20http://www.teradata.com/teradataforum/Topic10962-22-1.aspxHi,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_id12345 08:21:32 112345 08:21:35 112345 08:22:49 112345 08:23:00 112345 08:23:35 112345 12:59:32 212345 12:59:59 212345 13:02:17 2then 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_idFROM (SELECT T0."Account" AS C0 ,T0."call_id" AS C1 ,MIN(T0."Time_stamp") AS C2 ,COUNT(*) AS C3FROM IPSHARE."TMANNS_tmp1" T0GROUP BY T0."Account", T0."call_id") T0 ,(SELECT T0."Account" AS C0 ,T0."call_id" AS C1 ,MAX(T0."Time_stamp") AS C2FROM IPSHARE."TMANNS_tmp1" T0GROUP BY T0."Account",T0."call_id") T1 ,IPSHARE."TMANNS_tmp1" T2WHERE (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_count12345 1 08:21:32 08:21:32 08:23:35 512345 1 08:21:35 08:21:32 08:23:35 512345 1 08:22:49 08:21:32 08:23:35 512345 1 08:23:00 08:21:32 08:23:35 512345 1 08:23:35 08:21:32 08:23:35 512345 2 12:59:32 12:59:32 13:02:17 312345 2 12:59:59 12:59:32 13:02:17 312345 2 13:02:17 12:59:32 13:02:17 3If 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!TimTue, 18 Mar 2008 18:19:22 GMTTimMannshttp://www.teradata.com/teradataforum/Topic10962-22-1.aspxWhat 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 )Tue, 18 Mar 2008 01:31:20 GMTjoedsilvahttp://www.teradata.com/teradataforum/Topic10962-22-1.aspxForgot to say that there are other calls from other accounts in between the two calls in the example ...Account Timestamp12345 08:21:32 (call 1 start)12345 08:21:3512345 08:22:4912345 08:23:0012345 08:23:35 (call 1 end)(other calls from other accounts)12345 12:59:32 (call 2 start)12345 12:59:5912345 13:02:17 (call 2 end)Mon, 17 Mar 2008 12:16:11 GMTstan_leehttp://www.teradata.com/teradataforum/Topic10962-22-1.aspxHi,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 Timestamp12345 08:21:32 (call 1 start)12345 08:21:3512345 08:22:4912345 08:23:0012345 08:23:35 (call 1 end)12345 12:59:32 (call 2 start)12345 12:59:5912345 13:02:17 (call 2 end)Mon, 17 Mar 2008 12:07:40 GMTstan_lee