Use Teradata Recursions to build a Calendar

1
672
teradata recursion

 

teradata recursion

At times we need to join records in one table with a specified range of dates in a second table. One application which comes to my mind in the first place is to fake a snapshot history table (containing a snapshot date) based on a change history table (containing timelines limited by a start date and an end date). Here is one example:

CREATE TABLE Snapshot_History
(
PK INTEGER NOT NULL,
ATTRIB CHAR(10),
SNAPSHOT_DATE DATE
);

CREATE TABLE Change_History
(
PK INTEGER NOT NULL,
ATTRIB CHAR(10),
START_DATE DATE,
END_DATE DATE
);

The solution would probably look like this:

SELECT  t01.PK, t01.ATTRIB, t02, table?CALENDAR_DATE AS SNAPSHOT_DATE
FROM
Change_History t01
INNER JOIN
SYS_CALENDAR.CALENDAR t02
WHERE
t02.CALENDAR_DATE BETWEEN START_DATE AND END_DATE;

But what should you do if you do not have permissions to read from the SYS_CALENDAR.CAtable? You probably will have to create your calendar table, if you are not in the lucky situation that somewhere in your Teradata Data Warehouse exists already one as standard.

I came to hate building my calendar tables for several reasons, but principal reason is that each Data Warehouse I have seen until now ended up having calendar tables widely scattered across all subject areas. To make things even worse, this tables will show up in different shapes and naming conventions, making your life even harder.

Hence I started to research for a solution which should not need any real table at all. Further, from a performance point of view, I wanted to avoid the joining of tens of thousands of calendar dates despite only needing a few dates.

From my experience, you can end up with an awful performing query if Teradata is not able to apply early in the execution plan some partition elimination on the unneeded dates (This is especially valid for any Teradata Release before 13).

This was the moment when I started to play around with recursions and here is the result (you may probably want to transform the below SQL statement into a recursive view). My solution does not need any real table, and it only creates the range of dates you want:

WITH RECURSIVE DateRanges(CALENDAR_DATE) AS
(
SELECT DATE’2014-04-01′ AS CALENDAR_DATE
FROM
( SELECT * FROM (SELECT NULL AS X) X) DUMMYTABLE
UNION ALL
SELECT
CALENDAR_DATE + INTERVAL ‘1’ DAY
FROM
DateRanges
WHERE
CALENDAR_DATE < CURRENT_DATE
)
SELECT *
FROM DateRanges;

I am sure; you will find many other useful applications by adapting above SQL statement. I could, for example, imagine to change it in a way to deliver only the last day of each month for a certain time range.

I leave it to you as an easy task to post any further solutions.

Conclusion:

Teradata recursions can be helpful if your requirement is to create any ranks. Besides, the “dummy table creation” statement “SELECT * FROM (SELECT NULL AS X) X” permits to achieve this without any physical tables.

Our Reader Score
[Total: 4    Average: 4.5/5]
Use Teradata Recursions to build a Calendar written by Roland Wenzlofsky on April 25, 2014 average rating 4.5/5 - 4 user ratings

1 COMMENT

  1. Hi,
    Hope you are doing well.

    I created recursive view in TD. But i want to create view on top of that view , so i can utilize complete output for that view.

    I want to use SELECT query of with recursive clause in that view. So that i can get complete output of recursive query.

    Please let me know how to achieve this…any idea…!!!

LEAVE A REPLY

Please enter your comment!
Please enter your name here