Analytics
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

Junior Contributor

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