How to create macro variable in SQL/Teradata?

Question

I wrote a simple query, it aggregates transaction amount on each user ID for every 3 days,

select user_id, sum(tran_amt) as tot_amt from transaction_table
where tran_dt>=cast('2016-12-31' as date) - INTERVAL '2' DAY and tran_dt<='2016-12-31'

I want to calculate 3-day aggregated transaction amount for the whole month, from 12/01 to 12/31. I know how to do it in SAS, just replace the date '2016-12-31' with a macro variable, for example &tera_dt., something like this

%do i=1 %to 31;

call symput('tera_dt', "'"||put(intnx('day','1Jan2017'd,-&i,'b'),yymmdd10.)||"'")

But how can I create this date macro variable in Teradata? Thank you!

Or put it in another way, how to create a list of variables in Teradata? I want to create a macro variable called tera_dt, this variable contains dates from '2016-10-01' to '2016-10-31' total 31 dates, then I will run my query against this macro variable tera_dt. Thanks!

Tom Tom · Accepted Answer · 2017-01-06T02:16:48

I don't think you need code generation for this problem. You can join with a query against the system view sys_calendar.calendar. Something like this:

select a.user_id
     , b.calendar_date as date
     , sum(a.tran_amt) as tot_amt
from transaction_table a
inner join 
  (select calendar_date from sys_calendar.calendar
  where year_of_calendar=2016 and month_of_year=12) b
on a.tran_dt>=b.calendar_date - INTERVAL '2' DAY
  and a.tran_dt<=b.calendar_date
group by 1,2

How to create macro variable in SQL/Teradata?

1 Answers