17.05 - Example: EXPAND ON For an Anchored Interval - 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)

This example shows the use of an anchored interval for doing anchor period and anchor point expansions. For an anchor period expansion, the expanded period value must overlap the expanding period, while for an anchor point expansion, the begin value of the expanded period value must be contained in the expanding period, which is a more restrictive condition.

First create the sold_products table.

     CREATE SET TABLE sold_products, NO FALLBACK  (
       product_id       INTEGER,
       product_price    DECIMAL(10,2),
       product_duration PERIOD(DATE))
     PRIMARY INDEX (product_id);

The sold_products table contains the following rows.

product_id product_price product_duration
1000 100.00 2007-02-15, 2007-08-11
1001  99.99 2007-03-04, 2007-05-01

The following SELECT statement specifies an anchor period of MONTH_BEGIN. This is an anchor period expansion.

     SELECT product_id, product_price, product_duration, expd
     FROM sold_products
     EXPAND ON product_duration AS expd BY ANCHOR PERIOD MONTH_BEGIN;

The statement returns the following nine rows, with the original two rows highlighted in red:

product_id product_price product_duration expd
1000 100.00 2007-02-15,2007-08-11 2007-02-01,2007-03-01
1000 100.00 2007-02-15,2007-08-11 2007-03-01,2007-04-01
1000 100.00 2007-02-15,2007-08-11 2007-04-01,2007-05-01
1000 100.00 2007-02-15,2007-08-11 2007-05-01,2007-06-01
1000 100.00 2007-02-15,2007-08-11 2007-06-01,2007-07-01
1000 100.00 2007-02-15,2007-08-11 2007-07-01,2007-08-01
1000 100.00 2007-02-15,2007-08-11 2007-08-01,2007-09-01
1001  99.99 2007-03-04,2007-05-01 2007-03-01,2007-04-01
1001  99.99 2007-03-04,2007-05-01 2007-04-01,2007-05-01

For an anchor point expansion done on the same data, the shaded rows would not appear, as the following example shows.

The following table describes the difference between anchor point and anchor period expansions.

Expansion Type Description
Anchor period expansion Expanded period value must overlap the expanding period.
Anchor point expansion Begin value of the expanded period value must be contained within the expanding period.

Submit the following SELECT statement, which differs from the previous statement only in specifying the BEGIN bound function on product_duration instead of simply specifying the column name. This is an anchor point expansion done on the same data as the previous anchor period expansion.

     SELECT product_id, product_price, product_duration, BEGIN(expd)
     FROM sold_products
     EXPAND ON product_duration AS expd BY ANCHOR MONTH_BEGIN;

This statement returns seven rows, rather than nine, with the rows shaded in red from the previous example not appearing in the result set.

product_id product_price product_duration begin(expd)
1000 100.00 2007-02-15,2007-08-11 2007-03-01
1000 100.00 2007-02-15,2007-08-11 2007-04-01
1000 100.00 2007-02-15,2007-08-11 2007-05-01
1000 100.00 2007-02-15,2007-08-11 2007-06-01
1000 100.00 2007-02-15,2007-08-11 2007-07-01
1000 100.00 2007-02-15,2007-08-11 2007-08-01
1001  99.99 2007-03-04,2007-05-01 2007-04-01