Analytics Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print 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. Active Posters