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

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