fbpx

Use Teradata Recursions to build a Calendar

By Roland Wenzlofsky

April 25, 2014


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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • Hi,
    I hope you are doing well.

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

    I want to use the SELECT query 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…!!!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >