how to get monthly date in teradata

Question

I have a table called oth_mbr. I need to get 3 years of data month wise. current date minus 36 months and each month data betwwe from_dt and thru_dt needs to be fetched.

data shoulbe be in below formate

DATE , COLUMN A, COLUMN B
2017-08-01,FEP000,FPr
2017-09-01,FEP000,FPr
2017-10-01,FEP000,FPr
.
.
.
2020-08-01,FEP000,FPr

tried below query but this gives only one data

SEL distinct ADD_MONTHS(DATE - DATE MOD 100 + 1,- 36)  AS   DATE ,
COLUMN 1,COLUMN 2
OTH_MBR 
WHERE 
ADDD_MONTHS(DATE - DATE MOD 100 + 1,- 36)  BETWEEN FROM_DT AND THRU_DT

Result for above query is 
2017-08-01,FEP000,FPr

Answer 1

Teradata supports proprietary syntax for time series expansion:

select begin(pd), column1, column2
from OTH_MBR 
expand on period (from_dt, thru_dt) as pd
by anchor period month_begin
-- if you want to restrict the range of months
for period(ADD_MONTHS(current_date,- 36), current_date )

You can wrap it in a Derived Table/CTE for further processing:

with cte as 
 (
   select begin(pd) as mon, column1, column2
   from OTH_MBR 
   expand on period (from_dt, thru_dt) as pd
   by anchor period month_begin
   for period(ADD_MONTHS(current_date,- 36), current_date )
 )
select mon, count(*)
from cte
group by 1;