EXPAND ON Clause Examples | SQL SELECT Statements | Teradata Vantage - Example: Expansion on an Anchor Point Using WEEK_DAY - Analytics Database - Teradata Vantage

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.

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.