17.10 - Example: CURRENT_DATE Built-In Function in a CASE_N Expression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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