Example: Quarterly Partitioning Using CURRENT_DATE in a RANGE_N Expression - Analytics Database - 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);