All Forums Analytics
lxw 1 post Joined 07/12
12 Jul 2012
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

 

 

dnoeth 4628 posts Joined 11/04
12 Jul 2012

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

Dieter

You must sign in to leave a comment.