EXPAND ON Clause Examples | SQL SELECT Statements | Teradata Vantage - 17.05 - Example: Expansion on an Anchor Point Using WEEK_DAY - 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)

Create a table named tdate with the following definition.

     CREATE SET TABLE tdate (
       id       INTEGER,
       quantity INTEGER,
       pd       PERIOD(DATE))
     PRIMARY INDEX (id);

You insert two rows into tdate so its contents are as follows.

id quantity pd
11 110 2005-02-03, 2005-06-20
10 100 2004-01-03, 2004-05-20

Submit a SELECT statement against tdate that specifies an EXPAND ON clause anchored by a day of the week, Monday, so that each expanded row begins from a Monday, as specified in the statement, and the duration of each expanded period is seven days.

     SELECT id, BEGIN(bg)
     FROM tdate
     EXPAND ON pd AS bg BY ANCHOR MONDAY;

The database returns tdate details for each week of a given period, beginning on the first Monday from the eligible data, as you specified in the BY ANCHOR clause of the statement.

Because the first row in tdate starts on a Thursday, not a Monday, the expanded row starts on the next sequential Monday date, which is February 7, and then continues in weekly granular increments.

id

--

begin(bg)

---------

11 2005-02-07
11 2005-02-14
11 2005-02-21
11 2005-02-28
11 2005-03-07
11 2005-03-14
11 2005-03-21
11 2005-03-28
11 2005-04-04
11 2005-04-11
11 2005-04-18
11 2005-04-25
11 2005-05-02
11 2005-05-09
11 2005-05-16
11 2005-05-23
11 2005-05-30
11 2005-06-06
11 2005-06-13
10 2004-01-05
10 2004-01-12
10 2004-01-12
10 2004-01-19
10 2004-01-26
10 2004-02-02
10 2004-02-09
10 2004-02-16
10 2004-02-23
10 2004-03-01
10 2004-03-08
10 2004-03-15
10 2004-03-22
10 2004-03-29
10 2004-04-05
10 2004-04-12
10 2004-04-19
10 2004-04-26
10 2004-05-03
10 2004-05-10
10 2004-05-17