How Find out current Partition Range in Dynamic Partition Table in Teradata

Example 1:

select ResolvedCurrent_date,ConstraintText from DBC.PartitioningConstraintsV p

where p.databasename||'.'||p.tablename='ITCntlLoadReadyT.LR_DISTRIBUTION_EVENT_PIECE';

 

ResolvedCurrent_Date

ConstraintText

2018-05-14

CHECK (/*02 08 00*/ RANGE_N(Event_Dt_DD  BETWEEN ((ADD_MONTHS((DATE ),(-24 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE ),(-24 ))))( TITLE 'Day')( TITLE 'Day')( TITLE 'Day')))- (((((ADD_MONTHS((DATE ),(-24 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE ),(-24 ))))( TITLE 'Day')( TITLE 'Day')( TITLE 'Day'))(DATE))- (10106 (DATE))) MOD  7 )- 1 ( TITLE 'DayOfWeek()')) AND (((ADD_MONTHS((DATE ),(2 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE ),(2 ))))( TITLE 'Day')( TITLE 'Day')))- (((((ADD_MONTHS((DATE ),(2 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE ),(2 ))))( TITLE 'Day')( TITLE 'Day'))(DATE))- (10106 (DATE))) MOD  7 )+ 1 ( TITLE 'DayOfWeek()')))+ 1  EACH INTERVAL '7' DAY ) /*1 130+3687873665275687*/ IS NOT NULL AND RANGE_N(Location_Seq_Num  BETWEEN 1  AND 2500  EACH 1 ,

 NO RANGE OR UNKNOWN) /*2 2501+0*/ IS NOT NULL)

 

 Replace DATE with ReolvedCurrent_Date (Don't forget to replace "AND"  with "," )

SELECT

((ADD_MONTHS((DATE '2018-05-14'),(-24 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE '2018-05-14'),(-24 ))))( TITLE 'Day')( TITLE 'Day')( TITLE 'Day')))- (((((ADD_MONTHS((DATE '2018-05-14'),(-24 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE '2018-05-14'),(-24 ))))( TITLE 'Day')( TITLE 'Day')( TITLE 'Day'))(DATE))- (10106 (DATE))) MOD  7 )- 1 ( TITLE 'DayOfWeek()'))

,

(((ADD_MONTHS((DATE '2018-05-14'),(2 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE '2018-05-14'),(2 ))))( TITLE 'Day')( TITLE 'Day')))- (((((ADD_MONTHS((DATE '2018-05-14'),(2 )))- (EXTRACT(DAY FROM (ADD_MONTHS((DATE '2018-05-14'),(2 ))))( TITLE 'Day')( TITLE 'Day'))(DATE))- (10106 (DATE))) MOD  7 )+ 1 ( TITLE 'DayOfWeek()')))+ 1;

 

((ADD_MONTHS(2018-05-14, -24)-Day)-DayOfWeek())

(((ADD_MONTHS(2018-05-14, 2)-Day)-DayOfWeek())+1)

2016-04-25

2018-06-24

 

 Example 2:

 select ResolvedCurrent_date,ConstraintText from DBC.PartitioningConstraintsV p

where p.databasename||'.'||p.tablename='ITADST.CL_BASKET_TOTAL_A';

 

ResolvedCurrent_Date

ConstraintText

2018-05-01

CHECK (/*02*/ RANGE_N(Tran_Dt  BETWEEN ADD_MONTHS((DATE - (CAST(((EXTRACT(DAY FROM (DATE )))- 1 ) AS INTERVAL DAY(2)))),(-13 )) AND (ADD_MONTHS((DATE - (CAST(((EXTRACT(DAY FROM (DATE )))- 1 ) AS INTERVAL DAY(2)))),(1 )))- INTERVAL '1' DAY  EACH INTERVAL '1' MONTH ) IS NOT NULL AND RANGE_N(Store_Seq_Num  BETWEEN 1  AND 1000  EACH 1 , NO RANGE OR UNKNOWN) IS NOT NULL )

 

Replace DATE with ReolvedCurrent_Date (Don't forget to replace "AND"  with "," )


SELECT

ADD_MONTHS((DATE '2018-05-01' - (CAST(((EXTRACT(DAY FROM (DATE '2018-05-01' )))- 1 ) AS INTERVAL DAY(2)))),(-13 ))

,

(ADD_MONTHS((DATE '2018-05-01' - (CAST(((EXTRACT(DAY FROM (DATE '2018-05-01' )))- 1 ) AS INTERVAL DAY(2)))),(1 )))- INTERVAL '1' DAY;

 

ADD_MONTHS((2018-05-01-(EXTRACT(DAY FROM 2018-05-01)-1)), -13)

(ADD_MONTHS((2018-05-01-(EXTRACT(DAY FROM 2018-05-01)-1)), 1)- 1)

2017-04-01

2018-05-31

 

Comments