15.10 - Example: Quarterly Current and a Single Historical Partition Defined Using the CURRENT_DATE Built-In Function in a CASE_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 the sales table with sales from the latest 4 quarters in 4 known partitions and all other historical data in another partition. Notice that all 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 */);