17.00 - Example: Quarterly Partitioning Using CURRENT_DATE in a RANGE_N Expression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

This example partitions sales , with each quarter as a separate partition with CURRENT_DATE using RANGE_N. With this kind of partitioning, you can resolve CURRENT_DATE to a new date each quarter.

This CREATE TABLE request creates a PPI table with growing partitions at the end and shrinking partitions from the beginning. In this case, Vantage removes data from the partitions that are deleted because of the changed value for CURRENT_DATE when you reconcile the partitioning.

     CREATE SET TABLE sales, NO FALLBACK (
       store_ID  INTEGER,
       amount    DECIMAL(10, 2),
       region    CHARACTER(30),
       sale_date DATE FORMAT 'YYYY/MM/DD' NOT NULL)
     PRIMARY INDEX (store_ID) 
     PARTITION BY RANGE_N(sale_date BETWEEN CURRENT_DATE - INTERVAL '9'
                                            MONTH
                                    AND     CURRENT_DATE + INTERVAL '3'
                                            MONTH - INTERVAL '1' DAY
                                    EACH INTERVAL '3' MONTH);