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.
Advertisement
Answer
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.