Converting Oracle code to Teradata for counting/rolling up days to report on Q1-4 for a year

Database
N/A

Converting Oracle code to Teradata for counting/rolling up days to report on Q1-4 for a year

Hello,

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.

Code:

( 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

from dual

connect by level <= 7

union all

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

from dual

connect by level <= 4

union all

1 REPLY
N/A

Re: Converting Oracle code to Teradata for counting/rolling up days to report on Q1-4 for a year

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.