An Introduction to Teradata Recursions: Generating Date Ranges Without a Physical Table

 Introduction to Teradata Recursions

To link records from a primary table with a specific date range from a secondary table, a common requirement is to create a simulated history table with snapshot dates using a change history table with defined timelines. Consider the following illustration:

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 proposed solution would resemble this:

SELECT  t01.PK, t01.ATTRIB, t02.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;

You must create your own calendar table if you lack permission to access the SYS_CALENDAR table. Unless a standard table already exists within your Teradata Data Warehouse, this is likely your only option.

I dislike creating calendar tables for various reasons, primarily because every Data Warehouse I’ve encountered has scattered them throughout various subject areas. These tables exhibit varying shapes and naming conventions, making the task even more difficult.

Therefore, I began investigating a solution that didn’t require an actual table. Additionally, I aimed to optimize performance by minimizing the need to join numerous calendar dates, as I only required a handful.

In my experience, if Teradata cannot apply partition elimination to unnecessary dates early in the execution plan, query performance can suffer greatly. This is particularly true for Teradata releases before version 13.

After experimenting with recursions, I produced the following solution. To create a recursive view from the SQL statement below, please reference it accordingly. My solution generates the desired date range without using a physical table.

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;

You can find numerous beneficial applications by modifying the previous SQL statement. For instance, I suggest altering it to retrieve solely the last day of every month within a specified timeframe.

Feel free to post additional solutions in the comments.

Conclusion:

Teradata recursion can facilitate the creation of date ranges without needing physical tables by utilizing the “SELECT * FROM (SELECT NULL AS X) X” statement for dummy table creation.