Teradata - PeriodID arithmetics (PeriodID = date in YYYYMM format)

--declare prmPeriodID as integer;

--set prmPeriodID = 201112;

sel

       -- PeriodID, starting point, main

       201112 as PeriodID

       -- Year

       , cast( 201112 / 100 as integer) as PeriodYear

       -- Month

       , 201112 - ( cast( 201112 / 100 as integer) * 100 ) as PeriodMonth

       -- Date

       , cast

       (

              cast( 201112 / 100 as integer)

              || '-'

              || trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )

              || '-01'

              as date--varchar(50)

       ) as PeriodDate

       -- Previous Month

       , cast

       (

              cast( 201112 / 100 as integer)

              || '-'

              || trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )

              || '-01'

              as date--varchar(50)

       ) - interval '1' month

       as PeriodDateMinus1Month

       -- Next Month

       , cast

       (

              cast( 201112 / 100 as integer)

              || '-'

              || trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )

              || '-01'

              as date--varchar(50)

       ) + interval '1' month

       as PeriodDatePlus1Month

      

              -- NExt PeriodID

              , (

                     -- Year

                     (

                           extract(

                                  year from

                                  cast

                                  (

                                         cast( 201112 / 100 as integer)

                                         || '-'

                                         || trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )

                                         || '-01'

                                         as date--varchar(50)

                                  ) + interval '1' month

                           ) * 100

                     )

                     -- Month

                     + extract (

                           month from

                           cast

                           (

                                  cast( 201112 / 100 as integer)

                                  || '-'

                                  || trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )

                                  || '-01'

                                  as date--varchar(50)

                           ) + interval '1' month

                     )

              )

              as PeriodIDPlus1    

             

;