I’m looking to have a “Calendar” table created where it will give me the date between ranges ,say the past 5 years through today, or the past 5 years to the next 5 years. I’ve done something similar from data from a dataset but I need to ensure this table has dates that do not exist in the dataset I am querying from.
For example, here is what I’m doing today:
SELECT DISTINCT Date_YearMonthDay, CONCAT(TO_CHAR(Date_YearMonthDay, 'yyyy'), '-', TO_CHAR(Date_YearMonthDay, 'MM')) as CalYearMonth, TO_CHAR(Date_YearMonthDay, 'yyyy') as CalYear, TO_CHAR(Date_YearMonthDay, 'MON') as CalMonth FROM ( SELECT DISTINCT CreatedDate as Date_YearMonthDay FROM TableWithDates ) ORDER BY DATE_YearMonthDay desc
However, I’d like Dremio to provide me with the TableWithDates based on the range I’m looking for. Similar to what is posted here: https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql.
Sure I can do this the physical database/datasource, but I’d rather do it in Dremio for a variety of reasons.