Example: Creating a Time Series Using Expansion By an Interval Constant Value - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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