Example: CURRENT_DATE Built-In Function in a CASE_N Expression - Teradata Vantage

This example partitions the sales table with sales from the latest 4 quarters in 4 known partitions and all other historical data in another partition. New sales data is recorded in the first partition. This form of partitioning allows one current partition with all other partitions as history sales data.

     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 CASE_N(sale_date >= CURRENT_DATE /*latest data*/,
                         sale_date <  CURRENT_DATE 
                     AND sale_date >= CURRENT_DATE - INTERVAL '3'
                                      MONTH,/*previous quarter*/
                         sale_date < CURRENT_DATE - INTERVAL '3' MONTH 
                     AND sale_date >= CURRENT_DATE - INTERVAL '6' MONTH,
                         sale_date < CURRENT_DATE - INTERVAL '6' MONTH 
                     AND sale_date >= CURRENT_DATE - INTERVAL '9' MONTH,  
                     NO CASE /* older than 9 months data */);