Date Objects - Teradata

building semantic layer
1

Hi

I need to create following 6 date based objects in the universe…could you let me know the SQL for the same in Teradata

Current Month
Last Month
Current Quarter
Last Quarter
Year To Date
Last Year

I am specifically looking for Teradata SQL as i know how to do this in Oracle.

Thanks
Sudhir

goodmate (BOB member since 2004-07-12)

2

if you have no problem doing it in Oracle, check if, by chance four oracles objects / filter are working in terradata. If not, check your terradata sql reference to adapt it. it’s more a sql question like a pure bo one :wink:

bernard timbal :fr: (BOB member since 2003-05-26)

3

Bernard

As the functions in Teradata are different from Oracle to implement these objects, i am finding it a bit difficult to convert these objects into Teradata and seeking help from one of you guys

Cheers

goodmate (BOB member since 2004-07-12)

4

I think there are hardly very few people who can help you on this issue because most of the posts seems to be regarding oracle as their database.Hope, some teradata star would shed light no this thread.

venira :india: (BOB member since 2004-12-07)

5

All examples run as-is in SQL Assistant…

There are a number of date math tricks you can do with the extract and format statements. Here’s an example that returns a char formatted date as YYYYMM, then 5 date columns based on todays date:

SELECT
     CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4)) || CAST(CAST(CURRENT_DATE AS FORMAT 'MM') AS CHAR(2)) AS CURDATE_YYYYMM
    ,CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4)) || '-01-01' AS DATE) AS FIRST_DAY_CUR_YEAR
    ,CAST(CAST(EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE,-1)) AS CHAR(4)) || '-' || CAST(CAST(ADD_MONTHS(CURRENT_DATE,-1) AS FORMAT 'MM') AS CHAR(2)) || '-01' AS DATE) AS FIRST_DAY_LAST_MONTH
    ,CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4)) || '-' || CAST(CAST(CURRENT_DATE AS FORMAT 'MM') AS CHAR(2)) || '-01' AS DATE) - 1 AS LAST_DAY_LAST_MONTH
    ,CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) -1 AS CHAR(4)) || '-01-01' AS DATE) AS FIRST_DAY_LAST_YEAR
    ,CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4)) || '-01-01' AS DATE) - 1 AS LAST_DAY_LAST_YEAR

For quarter stuff, you’ll need to hit the system calendar. Here’s an example that returns all days for the prior quarter:

SELECT
     *
FROM 
     /* the calendar table (TABLE1) is here only for a usable example */
     /* this should be your table containing the date you're constraining */
     SYS_CALENDAR.CALENDAR TABLE1 
JOIN
     SYS_CALENDAR.CALENDAR CAL1
ON   
     TABLE1.CALENDAR_DATE = CAL1.CALENDAR_DATE
WHERE
     CAL1.QUARTER_OF_CALENDAR = (
     SELECT CAL2.QUARTER_OF_CALENDAR - 1 FROM SYS_CALENDAR.CALENDAR CAL2 WHERE CAL2.CALENDAR_DATE = CURRENT_DATE)

Hope this helps.

holmes (BOB member since 2004-04-27)

6

Thanks Holmes

I have a query regarding the quarter and is as follows

Cannot we quarter query be written in such a way that we only use the table which has a date field and thats the field based on which i want to differentiate the data like current quarter , last quarter etc., without actually using the calendat table

many thanks

goodmate (BOB member since 2004-07-12)

7

Hi

Could you let me know how to create a object for the last weeks data. The first day of last week should be Monday and the last day of last week should be sunday. The database that i am working on is Teradata…suggest how to create the object

Thanks
Sudhir

goodmate (BOB member since 2004-07-12)

8

This is a bit tricky because Teradata’s calendar runs from Sunday to Saturday. The way I would do it is create a view that grabs the last dates for last week and then adds 1 to them to reflect the shift. Also take into account the shift when looking at the current week:

CREATE VIEW MY_DB.LAST_WEEK (
     FIRST_DAY_LAST_WEEK
    ,LAST_DAY_LAST_WEEK)
AS
SELECT
     START_DATE.CALENDAR_DATE + 1
    ,END_DATE.CALENDAR_DATE + 1
FROM
     SYS_CALENDAR.CALENDAR START_DATE
    ,SYS_CALENDAR.CALENDAR END_DATE
WHERE
     START_DATE.WEEK_OF_CALENDAR = 
         (SEL 
               WEEK_OF_CALENDAR - 
               (CASE DAY_OF_WEEK WHEN 1 THEN 2 ELSE 1 END) 
          FROM 
               SYS_CALENDAR.CALENDAR 
          WHERE CALENDAR_DATE = CURRENT_DATE)
     AND
     START_DATE.DAY_OF_WEEK = 1
     AND
     END_DATE.WEEK_OF_CALENDAR = 
         (SEL 
               WEEK_OF_CALENDAR - 
               (CASE DAY_OF_WEEK WHEN 1 THEN 2 ELSE 1 END) 
          FROM 
               SYS_CALENDAR.CALENDAR 
          WHERE CALENDAR_DATE = CURRENT_DATE)
     AND
     END_DATE.DAY_OF_WEEK = 7

holmes (BOB member since 2004-04-27)

9

Hi,

Similarly I have a query, How can I find data between Quarter -60 (two months Prior from, first date of Quarter selected by user). For example if user selects Q1 - 2013 in report filter, then the query should pull data from 1 Nov 2012 to last date of quarter that is 31 March 2013. Query should have Year Query filter so in report filter user can select Q1, Q2, Q3 or Q4. I am using teradata.

Regards
Arun

asharma2010 (BOB member since 2010-07-26)

10

Moderator note @Arun:
Please do not cross-post. It leads to fragmented discussions and is against our forum rules. I deleted your other thread. Thanks.

Andreas :de: (BOB member since 2002-06-20)