DWHPro Insights

Date ranges and business calendars beyond recursion and Teradata SYS_CALENDAR

By Roland Wenzlofsky ·

Date ranges and business calendars beyond recursion and Teradata SYS_CALENDAR

In a previous article, Roland explained how to use recursion to build a data range without touching a physical table. This time, we explore an alternative approach that avoids recursion using a one-row metadata source whose AMP access depends on the plan. We can use this approach to build both a wide date range and a narrow one. We can also go beyond the limits of the Teradata SYS_CALENDAR table. The latter ends on 2100-12-31, which might be closer than we think :)).

With no further ado, here is the template query building atop EXPAND ON clause, a Teradata extension to the ANSI SQL:2011 standard.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
SELECT Cast(Begin(thePeriod) AS DATE) AS BUSINESS_DATE
FROM dbc.dbcinfov WHERE infokey = 'VERSION'
EXPAND ON Period(DATE '2101-01-01', DATE '2101-02-01') AS thePeriod BY ANCHOR PERIOD DAY;

EXPAND ON expands a PERIOD into anchored periods; it is not recursive SQL. The January 2101 fixture returned 31 dates. DBCInfoV access and AMP scope depend on the release and execution plan. Format the resulting DATE explicitly as YYYY-MM-DD when comparing endpoints across clients.

teradata sys_calendar

Using this template query as a starting point, one can efficiently extract more detailed periods (such as the first day of the month or specific weekdays) by adjusting the EXPAND ON parameters. This allows for better customization to match the needs of various business calendars or date ranges.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

3 thoughts on “Date ranges and business calendars beyond recursion and Teradata SYS_CALENDAR”

  1. Hello! There is a trick to do this without table access at all.
    Normally, it is prohibited to do EXPAND without volatiletempperm table reference, even if there is pseudo-table, so normally CTEs won’t help.
    But if we add any non-syntax sugar function anywhere in the request, it, suddenly, works. Like this:

    WITH CTE AS
    (SELECT PERIOD(Trunc(DATE ‘2020-09-01’, ‘DD’), Last_Day(DATE ‘2020-10-01’) + 1) AS prd)
    SELECT prd_ex FROM CTE t
    EXPAND ON prd AS prd_ex;

    This:

    WITH CTE AS
    (SELECT PERIOD(DATE ‘2020-09-01’, DATE ‘2020-10-31’ + 1) AS prd
    ,Greatest(1,1) AS Fake)
    SELECT prd_ex FROM CTE t
    EXPAND ON prd AS prd_ex;

    Or this:

    SELECT prd_ex
    FROM (SELECT 1 AS A) t
    WHERE Nvl(1,1) = 1
    EXPAND ON PERIOD(DATE ‘2020-09-01’, DATE ‘2020-10-31’ + 1) AS prd_ex;

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam