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.
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