Example: EXPAND ON MONTH_BEGIN and MONTH_END - 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™

This example shows how to use MONTH_BEGIN and MONTH_END in an EXPAND ON clause.

First create the ttimestamp table as follows.

     CREATE SET TABLE ttimestamp (
       id       INTEGER,
       quantity INTEGER,
       pd       PERIOD(TIMESTAMP(0)))
     PRIMARY INDEX (id);

Table ttimestamp contains the following rows.

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

When you specify an EXPAND ON clause by MONTH_BEGIN or MONTH_END, every expanded row starts from either the MONTH_BEGIN value or from the MONTH_END value for that month, and the granularity of each expanded period is one month. In this example, the table data is shown with the default session time zone set to INTERVAL ‘00:00’ HOUR TO MINUTE.

     SET TIME ZONE INTERVAL ‘00:00’ HOUR TO MINUTE;

The following SELECT statement specifies an EXPAND BY MONTH_BEGIN.

     SELECT id, quantity, BEGIN(bg)
     FROM ttimestamp
     EXPAND ON pd AS bg BY ANCHOR MONTH_BEGIN;

Each row is expanded at the default time literal value 00:00:00+00:00 for each MONTH_BEGIN value.

id qty bg
11 110 2005-03-01 00:00:00
11 110 2005-04-01 00:00:00
11 110 2005-05-01 00:00:00
11 110 2005-06-01 00:00:00
10 100 2004-02-01 00:00:00
10 100 2004-03-01 00:00:00
10 100 2004-04-01 00:00:00
10 100 2004-05-01 00:00:00

The following SELECT statement specifies an EXPAND BY ANCHOR MONTH_END, but is otherwise identical to the previous statement.

     SELECT id, quantity, BEGIN(bg)
     FROM ttimestamp
     EXPAND ON pd AS bg BY ANCHOR MONTH_END;

Each row is expanded at the default time literal value 23:59:59+00:00 at each month end.

id quantity bg
11 110 2005-02-28 23:59:59
11 110 2005-03-31 23:59:59
11 110 2005-04-30 23:59:59
11 110 2005-05-31 23:59:59
10 100 2004-01-31 23:59:59
10 100 2004-02-29 23:59:59
10 100 2004-03-31 23:59:59
10 100 2004-04-30 23:59:59