Need recursive SQL code

Database

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'));
3 REPLIES
Teradata Employee

Re: Need recursive SQL code

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

Re: Need recursive SQL code

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.


Re: Need recursive SQL code

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