17.05 - Example: Creating a Time Series Using Expansion By an Interval Constant Value - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

For additional examples of using the EXPAND ON clause in SELECT statements, see EXPAND ON Clause.

Suppose you create a table named employee with this definition.

     CREATE SET TABLE employee (
       emp_id       INTEGER,
       emp_name     CHARACTER(20) CHARACTER SET LATIN NOT CASESPECIFIC,
       job_duration PERIOD(DATE))
     PRIMARY INDEX (emp_id);

You insert 3 rows into the employee table:

employee    
emp_id emp_name job_duration
1001
Xavier
2002-01-10, 9999-12-31
1002
Ricci
2007-07-01, 9999-12-31
1003
Charles
2006-02-10, 2008-06-01

When you specify an EXPAND ON clause using an interval constant, the database expands each row based on the specified interval value, and the duration in the expanded period is the interval value.

So you now use an EXPAND ON clause to retrieve the employee details specifying an interval constant period.

     SELECT emp_id, emp_name, job_duration AS tsp
     FROM employee
     EXPAND ON job_duration AS tsp BY INTERVAL '1' YEAR
       FOR PERIOD(DATE '2006-01-01', DATE '2008-01-01');

The database returns employee details for each year of a given period, as you specified in the FOR clause of the SELECT statement.

The database returns a warning to the requestor that some rows in the expanded result might have an expanded period duration that is less than the duration of the specified interval.

  emp_id

------

emp_name

--------

job_duration

------------

tsp

---

  1003 Charles 2006-02-10, 2008-06-01 2006-02-10, 2007-02-10
  1003 Charles 2006-02-10, 2008-06-01 2007-02-10, 2008-01-01
  1002 Ricci 2007-07-01, 9999-12-31 2007-07-01, 2008-01-01
  1001 Xavier 2002-01-10, 9999-12-31 2006-01-01, 2007-01-01
  1001 Xavier 2002-01-10, 9999-12-31 2007-01-01, 2008-01-01