Recursive Sql Code

Analytics
Highlighted

Recursive Sql Code

Hello Experts,

I have following  requirement  where I need to calculate New_filed and no_of_days

For each cust _No , New filed ( new_filed) should amt added from all the rows from continuous dates when start_date is equal to previous row end_date

For example for first  3 rows , start date – 2/5/2015 and will continue till 4/10/2015 then add 10 + 5 + 7 == 22 ( since 4th row start_date is not equal to previous end_date)

It continuos …

For each cust _No , New filed (no_of_days) should  be  gap in no of days from end_date (continues days) minus from beginning of the start_date.

For example for first  3 rows , start date – 2/5/2015 and will continue till 4/10/2015 then  4/10/25 – 2/5/2015 == 64 ( since 4th row start_date is not equal to previous end_date)

It continuos …

Cust_no

start_date

end_date

amt

New_filed

no_of_days

1

2/5/2015

3/7/2015

10

22

64

1

3/7/2015

3/27/2015

5

22

64

1

3/27/2015

4/10/2015

7

22

64

1

7/20/2015

8/1/2015

7

10

21

1

8/1/2015

8/10/2015

3

10

21

1

9/14/2015

9/20/2015

5

5

6

1

10/11/2015

11/12/2015

9

9

32

9

1/5/2016

3/7/2016

5

11

126

9

3/7/2016

4/27/2016

2

11

126

9

4/27/2016

5/10/2016

4

11

126

9

7/20/2016

8/1/2016

4

9

21

9

8/1/2016

8/10/2016

5

9

21

9

9/14/2016

9/20/2016

7

7

6

9

10/11/2016

11/12/2016

4

30

241

9

11/12/2016

1/1/2017

6

30

241

9

1/1/2017

4/7/2017

10

30

241

9

4/7/2017

6/9/2017

10

30

241

 

I should achive this in BTEQ query.Thanks for your help

AmarReddy

1 REPLY
Teradata Employee

Re: Recursive Sql Code

Tested this in TDStudio, but it should at least be a good start for your BTEQ script:

 

select cust_no, start_date, end_date,amt 
from mt186048.basedata;

--cust_no	start_date	end_date	amt
--1			2015-02-05	2015-03-07	10
--1			2015-03-07	2015-03-27	5
--1			2015-03-27	2015-04-10	7
--1			2015-07-20	2015-08-01	7
-- ...

with recursive temp_table(cust_no,start_date,end_date,category) as (

	--use original start_date as a grouping category
	select cust_no, start_date, end_date, start_date
	from mt186048.basedata as base
	
	union all
	
	--add cateory from last row to this on if the dates line up
	select nex.cust_no, nex.start_date, nex.end_date, prev.category
	from mt186048.basedata as nex
	join temp_table as prev
	on nex.start_date = prev.end_date
	and nex.cust_no = prev.cust_no

)
select t.cust_no,t.start_date, t.end_date, t.category
	--sum all amounts in the category
	, sum(lkup.amt) over (partition by t.category) as new_filed
	--get length of time in the category
	, max(t.end_date) over (partition by t.category) 
		- min(t.start_date) over (partition by t.category) as no_of_days
from temp_table as t
join mt186048.basedata as lkup
	--Add in row ids and join by that
	on t.cust_no = lkup.cust_no and t.start_date = lkup.start_date and t.end_date = lkup.end_date
order by t.start_date
--keep only the first date category for each row
qualify row_number() over (
	--add in row ids and partition by that
	partition by t.cust_no,t.start_date, t.end_date
	order by t.category asc
) = 1;

--cust_no	start_date	end_date	amt	New_filed	no_of_days
--1			2015-02-05	2015-03-07	10	22			64
--1			2015-03-07	2015-03-27	5	22			64
--1			2015-03-27	2015-04-10	7	22			64
--1			2015-07-20	2015-08-01	7	10			21
--1			2015-08-01	2015-08-10	3	10			21
--1			2015-09-14	2015-09-20	5	5			6
--1			2015-10-11	2015-11-12	9	9			32
--9			2016-01-05	2016-03-07	5	11			126
--9			2016-03-07	2016-04-27	2	11			126
--9			2016-04-27	2016-05-10	4	11			126
--9			2016-07-20	2016-08-01	4	9			21
--9			2016-08-01	2016-08-10	5	9			21
--9			2016-09-14	2016-09-20	7	7			6
--9			2016-10-11	2016-11-12	4	30			241
--9			2016-11-12	2017-01-01	6	30			241
--9			2017-01-01	2017-04-07	10	30			241
--9			2017-04-07	2017-06-09	10	30			241

Michelle