15.10 - Example: Quarterly Partitioning Using CURRENT_DATE in a RANGE_N Expression - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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, Teradata Database 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);