17.05 - Uses of Anchor Period Expansions - 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)

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').