All Forums Database
rdeshpande 3 posts Joined 07/13
12 Jul 2016
Need recursive SQL code

Hi,
 I have data in following format:
I have first two columns  and I need to get third column using SQL/Recursive SQL.
The first two columns show mobile number and their call time to call center. I want to flag each call whether it is a repetitive call or not.
If the call from the same number is within 48 hours of first non repeated call , then I would flag it as repeated.
I have shown 17 rows below, in reality I have 3 millon rows to process. Could you please help.

Mobile Number

Call Time

Repeat/Non Repeat

A

2016/01/01 06:15:53

0

B

2016/01/01 18:29:29

0

B

2016/01/01 18:33:45

1

B

2016/01/01 19:14:02

1

B

2016/01/01 20:28:38

1

B

2016/01/05 20:51:48

0

B

2016/01/05 20:53:19

1

B

2016/01/10 20:54:28

0

C

2016/01/01 22:45:41

0

D

2016/01/02 17:33:12

0

E

2016/01/02 00:15:08

0

F

2016/01/01 19:51:09

0

F

2016/01/01 20:06:06

1

F

2016/01/01 21:24:47

1

F

2016/01/02 09:21:08

1

F

2016/01/03 20:21:08

0

F

2016/01/05 09:21:08

1

Here is script to create the above sample data:

CREATE TABLE  TestTable 
(
Mobile  VARCHAR(10),
CallTime  TIMESTAMP
)

INSERT INTO TestTable VALUES ('A',(TIMESTAMP '2016-01-01 06:15:53'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-01 18:29:29'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-01 18:33:45'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-01 19:14:02'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-01 20:28:38'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-05 20:51:48'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-05 20:53:19'));
INSERT INTO TestTable VALUES ('B',(TIMESTAMP '2016-01-10 20:54:28'));
INSERT INTO TestTable VALUES ('C',(TIMESTAMP '2016-01-01 22:45:41'));
INSERT INTO TestTable VALUES ('D',(TIMESTAMP '2016-01-02 17:33:12'));
INSERT INTO TestTable VALUES ('E',(TIMESTAMP '2016-01-02 00:15:08'));
INSERT INTO TestTable VALUES ('F',(TIMESTAMP '2016-01-01 19:51:09'));
INSERT INTO TestTable VALUES ('F',(TIMESTAMP '2016-01-01 20:06:06'));
INSERT INTO TestTable VALUES ('F',(TIMESTAMP '2016-01-01 21:24:47'));
INSERT INTO TestTable VALUES ('F',(TIMESTAMP '2016-01-02 09:21:08'));
INSERT INTO TestTable VALUES ('F',(TIMESTAMP '2016-01-03 20:21:08'));
INSERT INTO TestTable VALUES ('F',(TIMESTAMP '2016-01-05 09:21:08'));

 

AtardecerR0j0 71 posts Joined 09/12
13 Jul 2016
select mobile, calltime,
  max(calltime) over (partition by mobile
                         order by calltime asc
					  rows between 1 preceding and 1 preceding) calltime_previous,
  case when calltime_previous+interval '48' hour > calltime then 1 else 0 end as RepetitiveCall
from TestTable
order by 1, 2

 

Be More!!

rdeshpande 3 posts Joined 07/13
07 Aug 2016

Dear AtardecerR0j0,
Sorry for late reply. Thanks for your help. But their is one problem with your code.
For mobile number F here is the output from your code:

F 1/1/2016 19:51:09.000000 ? 0

F 1/1/2016 20:06:06.000000 1/1/2016 19:51:09.000000 1

F 1/1/2016 21:24:47.000000 1/1/2016 20:06:06.000000 1

F 1/2/2016 09:21:08.000000 1/1/2016 21:24:47.000000 1

F 1/3/2016 20:21:08.000000 1/2/2016 09:21:08.000000 1

F 1/5/2016 09:21:08.000000 1/3/2016 20:21:08.000000 1

 

Whereas I want it to be

F 1/1/2016 19:51:09.000000 ? 0

F 1/1/2016 20:06:06.000000 1/1/2016 19:51:09.000000 1

F 1/1/2016 21:24:47.000000 1/1/2016 20:06:06.000000 1

F 1/2/2016 09:21:08.000000 1/1/2016 21:24:47.000000 1

F 1/3/2016 20:21:08.000000 1/2/2016 09:21:08.000000 0

F 1/5/2016 09:21:08.000000 1/3/2016 20:21:08.000000 1

 

The 0 for F on 3rd Jan is because it is NOT withing 48 hours of the previous 0 which is for 2016/01/01 19:51:09
The repeated calls are ignored as if they don't exist.
 
Can you please help.

 

 

 

rdeshpande 3 posts Joined 07/13
07 Aug 2016

I asked the same question on Oracle forum and got the answer but unable to convert the syntax to Teradata.
Here is the link.
https://community.oracle.com/thread/3949146

You must sign in to leave a comment.