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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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);