I know I am a little late to the party, but for every project I start for a new company it seems I need to create a new dates table. Here is the stock snowflake code I use to write the dates table. I use some parameters to reduce errors and set up the fiscal offsets. (This is helpful when you need additional dimensions that Looker doesn’t provide like fiscal week of year)
– set the fiscal month offset
/*
set fiscal_month_offset = 11;
set end_date = TO_DATE(‘2031-01-31’,‘YYYY-MM-DD’);
set start_date = TO_DATE(‘2017-02-01’,‘YYYY-MM-DD’);
set num_days = (Select datediff(day, $start_date, $end_date)+1); */
/*
With dates AS (
– we will use the date add, and the generator to make this work
select
dateadd(
day,
‘-’ || row_number() over (order by null),
dateadd(day, 1, $end_date)
) as full_date
from table (generator(rowcount => $num_days))
),
– now that we have a generator and a list of dates we will set the periods
dates_periods AS (
SELECT full_date,
– construct year and fy
year(full_date) AS calendar_year,
year(dateadd(‘month’, $fiscal_month_offset, full_date)) AS fiscal_year,
– calendar quarter and fiscal quarter
quarter(full_date) AS calendar_quarter,
CONCAT(year(full_date),‘-Q’,quarter(full_date)) AS calendar_year_quarter,
QUARTER(dateadd(‘month’,$fiscal_month_offset,date_trunc(‘month’, full_date))) as fiscal_quarter,
CONCAT(year(dateadd(‘month’, $fiscal_month_offset, full_date)),
‘-Q’,
quarter(dateadd(‘month’,$fiscal_month_offset,date_trunc(‘month’, full_date)))) AS fiscal_year_quarter,
– calendar month fiscal month
MONTH(full_date) AS calendar_month_num,
CONCAT(year(full_date),‘-’, lpad(month(full_date),2,‘0’)) as cal_month_year,
MONTH(dateadd(‘month’,$fiscal_month_offset,date_trunc(‘month’, full_date))) as fiscal_month_number,
CONCAT(year(full_date),‘-’, lpad(month(dateadd(‘month’,$fiscal_month_offset,date_trunc(‘month’, full_date))),2,‘0’)) as fiscal_month_year,
MONTHNAME(full_date) as month_name,
– calendar week num and fiscal week num
WEEKISO(full_date) as calendar_week_num,
CONCAT(year(full_date),‘-’, lpad(WEEKISO(full_date),2,‘0’)) as cal_week_year,
WEEKISO(dateadd(‘day’, 2,dateadd(‘month’, $fiscal_month_offset, full_date))) as fiscal_week_num, – this is a bid dodgy
CONCAT(year(dateadd(‘month’, $fiscal_month_offset, full_date)),‘-’,
lpad(WEEKISO(dateadd(‘day’, 2,dateadd(‘month’, $fiscal_month_offset, full_date))),2,‘0’)) as fiscal_week_year,
– day stats
DAYOFWEEKISO(full_date) as day_of_week_num_iso,
DAYOFWEEK(full_date) as day_of_week_num,
DAYNAME(full_date) as day_name
FROM dates
),
– now we grab first and last day for fliters
fiscal_year_dates AS (
SELECT fiscal_year,
min(full_date) as first_day,
max(full_date) as last_day
from dates_periods
GROUP BY 1
),
– now we grab first and last day for fliters
calendar_quarter_dates AS (
SELECT calendar_year_quarter,
min(full_date) as first_day,
max(full_date) as last_day
from dates_periods
GROUP BY 1
),
– now we grab first and last day for fliters
fiscal_quarter_dates AS (
SELECT fiscal_year_quarter,
min(full_date) as first_day,
max(full_date) as last_day
from dates_periods
GROUP BY 1
),
– now we grab first and last day for fliters
calendar_month_dates AS (
SELECT cal_month_year,
min(full_date) as first_day,
max(full_date) as last_day
from dates_periods
GROUP BY 1
),
full_dates as (
SELECT
d_dates.full_date,
– calendar year
d_dates.calendar_year,
date_from_parts(d_dates.calendar_year,‘01’, ‘01’) as fist_day_of_calendar_year,
date_from_parts(d_dates.calendar_year,‘12’, ‘31’) as last_day_of_calendar_year,
row_number() OVER (partition by d_dates.calendar_year order by full_date ASC) AS day_of_calendar_year_num,
– fiscal year
d_dates.fiscal_year,
fiscal_year_dates.first_day as first_day_of_fiscal_year,
fiscal_year_dates.last_day as last_day_of_fiscal_year,
row_number() OVER (partition by d_dates.fiscal_year order by full_date ASC) AS day_of_fiscal_year,
– calendar quarter
d_dates.calendar_quarter,
d_dates.calendar_year_quarter,
calendar_quarter_dates.first_day as first_day_of_calendar_quarter,
calendar_quarter_dates.last_day as last_day_of_calendar_quarter,
row_number() OVER (partition by d_dates.calendar_year_quarter order by full_date ASC) AS day_of_calendar_quarter_num,
– fiscal quarter
d_dates.fiscal_quarter,
d_dates.fiscal_year_quarter,
fiscal_quarter_dates.first_day as first_day_of_fiscal_quarter,
fiscal_quarter_dates.last_day as last_day_of_fiscal_quarter,
row_number() OVER (partition by d_dates.fiscal_year_quarter order by full_date ASC) AS day_of_calendar_quarter_num,
– calendar month (first and last day of month same for fiscal and calendar months)
d_dates.month_name,
d_dates.calendar_month_num,
d_dates.cal_month_year,
calendar_month_dates.first_day as first_day_of_month,
calendar_month_dates.last_day as last_day_of_month,
row_number() OVER (partition by d_dates.cal_month_year order by full_date ASC) AS day_of_month_num,
– fiscal month
d_dates.fiscal_month_number,
d_dates.fiscal_month_year,
– calendar week of year (day of week num same for fiscal and calendar week)
d_dates.calendar_week_num,
cal_week_year,
d_dates.day_of_week_num_iso,
d_dates.day_of_week_num,
d_dates.day_name,
– fiscal week of year
d_dates.fiscal_week_num,
d_dates.fiscal_week_year
FROM dates_periods as d_dates
LEFT JOIN fiscal_year_dates on d_dates.fiscal_year = fiscal_year_dates.fiscal_year
LEFT JOIN calendar_quarter_dates on d_dates.CALENDAR_YEAR_QUARTER = calendar_quarter_dates.CALENDAR_YEAR_QUARTER
LEFT JOIN fiscal_quarter_dates on d_dates.fiscal_year_quarter = fiscal_quarter_dates.fiscal_year_quarter
LEFT JOIN calendar_month_dates on d_dates.cal_month_year = calendar_month_dates.cal_month_year
)
SELECT *
FROM full_dates