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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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.

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