How to generate dynamic table having begin month date and end month date Teradata or SAS SQL

Question

I’d like to generate a dynamic Table with the start date of a month as a column and the end date of the month as another column.

Ideally, I’d like to provide two years, f.e. 2016 and 2021. The output I’d like to have when providing these two years is the following:

Begin_of_Month End_of_Month
2016-01-01     2016-01-31
2016-02-01     2016-02-29
.
.
.
2021-12-01     2021-12-31

Kindly note that I require the output for all years from 2016 to 2021. In my example above this would mean that 2017 2018 2019 2020 should be included.

I have tried to play with a time series function of Teradata but failed to get results.

The solution I tried to recreate in Teradata is this one: How to generate calendar table having begin month date and end month Date

Furthermore, I have tried the EXPAND ON PERIOD time series function of Teradata.

Answer 1

I'm sure there are some fancy ways of doing this, but I think just hitting up the built-in calendar table is probably the easiest:

SELECT DISTINCT 
    min(calendar_date) OVER (PARTITION BY year_of_calendar, month_of_calendar) as start_of_month, 
    max(calendar_date) OVER (PARTITION BY year_of_calendar, month_of_calendar) as end_of_month  
FROM sys_calendar.calendar
WHERE year_of_calendar BETWEEN 2016 and 2021

To do this without a table reference, it gets a little ugly. EXPAND ON seems like an obvious route, but it errors if there is no table reference in the FROM clause. UNION suffers from the same issue, but we can cheat with UNION by using a cte. EXPAND ON is more picky and to trick it we can hijack Teradata's JSON_TABLE feature:

SELECT BEGIN(dt), PRIOR(END(dt))
FROM JSON_TABLE
    ( 
        ON (SELECT 1 as id, NEW JSON('{"startdate":"2016-01-01","enddate":"2021-12-31"}') jd)
        USING 
            rowexpr('$')
            colexpr('[{"jsonpath" : "$.startdate", "type" : "DATE"},
                      {"jsonpath" : "$.enddate", "type" : "DATE"}]')
    ) as jt(id, startdate, enddate)
EXPAND ON PERIOD(startdate, enddate) as dt BY ANCHOR MONTH_BEGIN

You could also go with a recursive CTE to build out the months, which feels less hacky, but takes longer to generate.

WITH startend AS
(
    SELECT 
        DATE '2016-01-01' periodstartdate,
        DATE '2021-12-31' AS periodenddate
)
,RECURSIVE months AS
(
    SELECT periodstartdate,
        periodenddate,
        periodstartdate as monthstartdate,
        1 as monthoffset
    FROM startend
    UNION ALL
    SELECT periodstartdate,
        periodenddate,
        ADD_MONTHS(periodstartdate, monthoffset),
        monthoffset + 1
    FROM 
        months 
    WHERE monthoffset < months_between(periodenddate, periodstartdate) 
)
SELECT monthstartdate, monthstartdate + INTERVAL '1' MONTH - INTERVAL '1' DAY as monthenddate from months;

I'd be very interested if there is a more elegant way to pull this off. Without dual or sequence generation like are present in other RDBMS, the options to build data sets with no table reference are pretty limited.

Answer 2

Normally EXPAND ON only works when a table is accessed in FROM, but applying some function like TRUNC or TO_DATE fools the optimizer:

WITH dummy AS 
 (
   SELECT 
      2016 AS yr_start
     ,2021 as yr_end
     ,TO_DATE(TRIM(yr_start) || '-01-01') AS pd_start
     ,TO_DATE(TRIM(yr_end+1) || '-01-01') AS pd_end
 ) 
SELECT 
   BEGIN(pd) AS Begin_of_Month
  ,LAST(pd)  AS End_of_Month
FROM dummy
EXPAND ON PERIOD(pd_start, pd_end) AS pd
          BY INTERVAL '1' MONTH

Answer 3

If you are going to do this in SAS then there is no need for SQL.

data want;
  do year=2016 to 2021;
    do month=1 to 12;
      start_of_month=mdy(month,1,year);
      end_of_month=intnx('month',start_of_month,0,'e');
      output;
    end;
  end;
  format start_of_month end_of_month yymmdd10.;
  drop year month;
run;