Generating consecutive months based on tenure

Analytics
lxw
N/A

Generating consecutive months based on tenure

I am new to teradata and trying to figure out how to do this efficiently. I have a data for each customer such as

customer_ID    tenure     Endyyyymm

1                      4            201203

2                      8          201201

I need to generate consecutive months for each customer till the endyyyymm for the span of tenure and if tenure was longer than 5 then only 5 previous consecutive months will be needed.

So for above data, the output data should look like

customer ID     yyyymm

1                     201112

1                     201201

1                     201202

1                      201203          

2                    201109

2                     201110

2                     201111

2                    201112

2                     201201

the 1st customer have 4 rows of consecutive months becuase it has tenure=4 till 201203. 2nd customer have 5 rows of consecutive months because its tenure>5 so only 5 rows were needed. 

Anyone help me how to write a code to achive this? I have been struggling for quite a while without any clue.

Ling

1 REPLY
Senior Apprentice

Re: Generating consecutive months based on tenure


Hi Ling,



when you're on TD13.10 you can solve it with "Time Series Expansion" for PERIODs, it's just a bit more complicated because of the partial date.



I assume Endyyyymm is a CHAR(6) (if it's numeric it must be adjusted):



SELECT customer_id, END(newP) (FORMAT 'yyyymm') (CHAR(6))
FROM
(
SELECT customer_id,
PERIOD(ADD_MONTHS((Endyyyymm (DATE, FORMAT 'yyyymm')), -CASE WHEN tenure > 5 THEN 5 ELSE tenure END), Endyyyymm (DATE, FORMAT 'yyyymm')) AS p,
newP
FROM tab
EXPAND ON p AS newP BY ANCHOR MONTH_BEGIN
) AS dt


In older releases this will need a Cross Join to a calendar table...



Dieter