16.20 - Example: Quarterly Current and a Single Historical Partition Defined Using the CURRENT_DATE Built-In Function in a CASE_N Expression - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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