CODEWITHSUNDEEP
sqlteradata
starfly
starfly

Reputation: 83

Teradata SQL: Create volatile table with multiple rows automatically populated based on logic

I've created a script that creates a volatile table with 3 columns, and inserts 5 rows:

create multiset volatile table dr (
  period int
, st_date date
, end_date date
) 
primary index (period) on commit preserve rows;

insert into dr (period,st_date,end_date)
select *
from( select *
      from  (select   '201712' period
                    , '2017-10-01' st_date
                    , '2017-12-31' end_date
            )t1
      union all
      select *
      from  (select   '201612' period
                    , '2016-10-01' st_date
                    , '2016-12-31' end_date
            )t2
      union all
      select *
      from  (select   '201512' period
                    , '2015-10-01' st_date
                    , '2015-12-31' end_date
            )t3
      union all
      select *
      from  (select   '201412' period
                    , '2014-10-01' st_date
                    , '2014-12-31' end_date
            )t4
      union all
      select *
      from  (select   '201312' period
                    , '2013-10-01' st_date
                    , '2013-12-31' end_date
            )t5
    )t

I'll be joining this table to a couple other tables for date ranges.

Here I'm manually typing the information for each inserted row, which is kind of cumbersome and inefficient.

Is there a more automated way to do this, and have it all run off of one date, such as 2017-12-31? I can work out the logic myself, but just not sure how to structure the insert statement to allow for this kind of logic.

Thanks!

EDIT I hope to be able to automate this for any 3 consecutive month period, also when crossing years (e.g. 2017-11-01 to 2018-01-31, or 2017-12-01 to 2018-02-28), and then going back 5 years for that consecutive 3 month period.

Upvotes: 0

Views: 6266

Answers (2)

dnoeth
dnoeth

Reputation: 60472

I would suggest using some logic based on TRUNC/ADD_MONTHS/LAST_DAY in a macro:

REPLACE MACRO testmac (in_date DATE)
AS
 (
   CREATE SET VOLATILE TABLE dr -- no need for MULTISET
   AS 
    (
      SELECT year_of_calendar * 100 + month_of_year AS PERIOD,
         Add_Months(calendar_date,-2) AS st_date, 
         Last_Day(calendar_date) AS end_date
      FROM sys_calendar.CALENDAR
      WHERE year_of_calendar -- current_month and two previous months
            BETWEEN Extract(YEAR From Add_Months(:in_date,-48))
                AND Extract(YEAR From :in_date)
        AND month_of_year = Extract(MONTH From :in_date)
        AND day_of_month = 1 -- only one row per year
    ) WITH DATA  
      UNIQUE PRIMARY INDEX (PERIOD)
      ON COMMIT PRESERVE ROWS;
 );

EXEC testmac(DATE '2018-01-22');

You could also apply a recursive query or EXPAND ON.

Edit:

EXPAND ON is nice & short :-)

SELECT Extract(YEAR From End(pd)) * 100 + Extract(MONTH From End(pd)) AS PERIOD
  ,Trunc(Add_Months(End(pd),-2), 'mon') AS st_date
  ,Last_Day(End(pd)) AS end_date
FROM sys_calendar.CALENDAR               -- specify the date once
WHERE calendar_date = DATE '2018-01-22'  -- or :in_date in the macro
EXPAND ON PERIOD(Add_Months(calendar_date,-60), calendar_date) AS pd
BY INTERVAL '1' YEAR -- one row per year

Upvotes: 2

JNevill
JNevill

Reputation: 50064

You can use sys_calendar.calendar table to derive these periods, getting a little crafty with some window functions:

SELECT distinct
    year_of_calendar * 100 + max(month_of_year) OVER (PARTITION BY year_of_calendar) as "period",
    min(calendar_date) OVER (PARTITION BY year_of_calendar) as st_date,
    max(calendar_date) OVER (PARTITION BY year_of_calendar) as end_date
FROM sys_calendar.calendar 
WHERE month_of_year BETWEEN 10 AND 12
    AND year_of_calendar BETWEEN 2013 AND 2017

+--------+------------+------------+
| period |  st_date   |  end_date  |
+--------+------------+------------+
| 201312 | 2013-10-01 | 2013-12-31 |
| 201412 | 2014-10-01 | 2014-12-31 |
| 201512 | 2015-10-01 | 2015-12-31 |
| 201612 | 2016-10-01 | 2016-12-31 |
| 201712 | 2017-10-01 | 2017-12-31 |
+--------+------------+------------+

Wrapping this into a CREATE TABLE statement:

CREATE MULTISET VOLATILE TABLE dr AS
(
    SELECT distinct
        year_of_calendar * 100 + max(month_of_year) OVER (PARTITION BY year_of_calendar) as "period",
        min(calendar_date) OVER (PARTITION BY year_of_calendar) as st_date,
        max(calendar_date) OVER (PARTITION BY year_of_calendar) as end_date
    FROM sys_calendar.calendar 
    WHERE month_of_year BETWEEN 10 AND 12
        AND year_of_calendar BETWEEN 2013 AND 2017
) 
WITH DATA
PRIMARY INDEX ("period")
ON COMMIT PRESERVE ROWS;

Upvotes: 2

Related Questions