Uses of Anchor Period Expansions - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

The expanded rows correspond to all anchor periods that overlap the expansion period. The key difference between anchor period expansion and anchor point expansion is that for anchor point expansion, the anchor point, which is the beginning bound of an anchor period, must occur within the expansion period, while for anchor period expansions, the anchor period must overlap the expansion period.

Following are the valid uses of anchor period expansions:
  • The expansion period has a duration that is less than that of the expansion interval.

    For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2011-08-14', DATE '2011-08-17'), then the expansion period starts on Tuesday, ends on Friday of the same week, and the expanded row is a singleton with a resulting anchor period of PERIOD(DATE '2011-08-13', DATE'2011-08-20').

    In this case, there is only one expanded row for the input row.

  • The expansion period is not aligned with an anchor period, but the duration of the expansion period is greater than the expansion interval.

    Such a row produces more than one expanded row in its result.

    For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2011-08-15', DATE '2011-08-25').

    In this case, the expanded result has two anchor periods, PERIOD(DATE '2011-08-13', DATE '2011-08-20') and PERIOD(DATE '2011-08-20', DATE '2011-08-27').

  • The expanding period is aligned with an anchor period and its duration is an exact multiple of the expansion interval.

    For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2011-08-20', DATE '2011-08-27'), then the expanded row contains PERIOD(DATE '2011-08-20', DATE '2011-08-27').