I am new to Teradata and I am trying to convert an Oracle sub-query that counts the days, week, month and year and rolls that up to report by Q1-YYYY and by site for a company. I understand what they wrote in Oracle but cannot figure out how to do it in Teradata. I have seen many things saying use recurvise coding etc... but really dont understand the examples given. If someone can help with this part i can figure out how to change the other parts they wrote. I appreciate any help that someone can give. I included two parts of their sub-select statement from the where clause. Thanks again for your help.
( SELECT 'Day' as Time_Period,
to_char( trunc( sysdate ) - level, 'mm/dd' ) as Period,
trunc( sysdate ) - level as start_dt,
trunc( sysdate ) - level + 86399/86400 as end_dt
connect by level <= 7
-- RETURNS LAST 4 QUARTERS (BEFORE TODAY)
SELECT 'Quarter' as Time_Period,
'Q' || to_char( add_months( trunc( sysdate, 'Q' ), - ( level * 3 ) ), 'Q-YYYY' ) as Period,
add_months( trunc( sysdate, 'Q' ), - ( level * 3 ) ) as start_dt,
add_months( trunc( sysdate, 'Q' ), - ( ( level - 1 ) * 3 ) ) - 1 + 86399/86400 as end_dt
connect by level <= 4
I found a way to handle this problem. Hopefully someone else can use this if they run into some old code.
I used cast(extract(month from adate ) +2 )/ 3 as char(1). This will get the month for the quarter that it falls in.