generate_date_calendar_with_holidays - BigQuery

CALL `justfunctions.eu.generate_date_calendar_with_holidays`("justfunctions.test.date_calendar","2020-01-01","2030-01-01","GR")
                                            
/*--Output--
- date_id: 20200101
- date: 2020-01-01
- epoch: 1577836800
- day_suffix: 1th
- day_name: Wednesday
- day_name_abbr: Wed
- day_of_week: 4
- day_of_month: 1
- day_of_quarter: 1
- day_of_year: 1
- week_of_month: 1
- week_of_year: 1
- week_of_year_iso: 2020-W01-3
- month: 1
- month_name: January
- month_name_abbr: Jan
- quarter: 1
- quarter_name: Q1
- year: 2020
- start_of_week: 2019-12-30
- start_of_week_monday: 2019-12-29
- start_of_week_saturday: 2019-12-28
- start_of_month: 2020-01-01
- start_of_midmonth: 2020-01-15
- start_of_quarter: 2020-01-01
- start_of_year: 2020-01-01
- end_of_week: 2020-01-03
- end_of_month: 2020-01-31
- end_of_quarter: 2020-03-31
- end_of_year: 2020-12-31
- start_of_fiscal_year: 2019-10-01
- end_of_fiscal_year: 2020-09-30
- yyyymm: 202001
- yyyymmdd: 20200101
- month_desc: 2020-Jan
- quarter_desc: 2020-Q1
- week_desc: 2020-W1
- is_weekend: FALSE
- season: Winter
- start_of_season: 2019-12-01
- holiday_name: Πρωτοχρονιά
- holiday_country: gr
- special_day_name : New Year's Day
- moon_illumination : 29.95
- days_in_a_month: 31
- is_leap_year: 1

*/

CREATE OR REPLACE PROCEDURE `your_project_id.your_dataset_id.generate_date_calendar_with_holidays`(`table_name` string, `start_date` string, `end_date` string, `country` string)
options(
    description = '''Generates a complete date calendar table named <table_name> with holidays for the specified <country>, from <start_date> to <end_date>. Holidays are available between 2000-2100.'''
)
BEGIN

-- Drop the table if it exists
EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || table_name;

-- Create the date_calendar table
EXECUTE IMMEDIATE 'CREATE TABLE ' || table_name || ' (date_id INT64 NOT NULL, date DATE NOT NULL, epoch INT64 NOT NULL, day_suffix STRING NOT NULL, day_name STRING NOT NULL, day_name_abbr STRING NOT NULL, day_of_week INT64 NOT NULL, day_of_month INT64 NOT NULL, day_of_quarter INT64 NOT NULL, day_of_year INT64 NOT NULL, week_of_month INT64 NOT NULL, week_of_year INT64 NOT NULL, week_of_year_iso STRING NOT NULL, month_ INT64 NOT NULL, month_name STRING NOT NULL, month_name_abbr STRING NOT NULL, quarter INT64 NOT NULL, quarter_name STRING NOT NULL, year INT64 NOT NULL, start_of_week DATE NOT NULL, start_of_week_monday DATE NOT NULL,start_of_week_saturday DATE NOT NULL, start_of_month DATE NOT NULL, start_of_midmonth DATE NOT NULL, start_of_quarter DATE NOT NULL, start_of_year DATE NOT NULL, end_of_week DATE NOT NULL, end_of_month DATE NOT NULL, end_of_quarter DATE NOT NULL, end_of_year DATE NOT NULL,start_of_fiscal_year DATE NOT NULL,end_of_fiscal_year DATE NOT NULL, yyyymm STRING NOT NULL, yyyymmdd STRING NOT NULL, month_desc STRING, quarter_desc STRING, week_desc STRING, is_weekend BOOL NOT NULL, season STRING, start_of_season DATE, holiday_name STRING, holiday_country STRING, special_day_name STRING, moon_illumination FLOAT64,days_in_month INT64, is_leap_year INT64);';

-- Populate the table
EXECUTE IMMEDIATE 'INSERT INTO ' || table_name || ' WITH date_range AS (SELECT date FROM UNNEST(GENERATE_DATE_ARRAY("' || start_date || '","' || end_date  || '")) AS date) SELECT CAST(FORMAT_DATE("%Y%m%d", date) AS INT64) AS date_id, date AS date, UNIX_SECONDS(TIMESTAMP(date)) AS epoch, CONCAT(CAST(EXTRACT(DAY FROM date) AS STRING), "th") AS day_suffix, FORMAT_DATE("%A", date) AS day_name, FORMAT_DATE("%a", date) AS day_name_abbr, EXTRACT(DAYOFWEEK FROM date) AS day_of_week, EXTRACT(DAY FROM date) AS day_of_month, EXTRACT(DAY FROM date) - EXTRACT(DAY FROM DATE_TRUNC(date, QUARTER)) + 1 AS day_of_quarter, EXTRACT(DAYOFYEAR FROM date) AS day_of_year, EXTRACT(WEEK FROM date) - EXTRACT(WEEK FROM DATE_TRUNC(date, MONTH)) + 1 AS week_of_month, CAST(FORMAT_DATE("%V", date) AS INT64) AS week_of_year, FORMAT_DATE("%G-W%V-%u", date) AS week_of_year_iso, EXTRACT(MONTH FROM date) AS month, FORMAT_DATE("%B", date) AS month_name, FORMAT_DATE("%b", date) AS month_name_abbr, EXTRACT(QUARTER FROM date) AS quarter, CONCAT("Q", CAST(EXTRACT(QUARTER FROM date) AS STRING)) AS quarter_name, EXTRACT(YEAR FROM date) AS year, DATE_TRUNC(date, WEEK(MONDAY)) AS start_of_week, DATE_TRUNC(date, WEEK(MONDAY)) AS start_of_week_monday ,DATE_TRUNC(date, WEEK(SATURDAY)) AS start_of_week_saturday, DATE_TRUNC(date, MONTH) AS start_of_month, DATE_ADD(DATE_TRUNC(date, MONTH), INTERVAL 14 DAY) AS start_of_midmonth, DATE_TRUNC(date, QUARTER) AS start_of_quarter, DATE_TRUNC(date, YEAR) AS start_of_year, DATE_ADD(DATE_TRUNC(date, WEEK(SATURDAY)), INTERVAL 6 DAY) AS end_of_week, LAST_DAY(date, MONTH) AS end_of_month, LAST_DAY(date, QUARTER) AS end_of_quarter, LAST_DAY(date, YEAR) AS end_of_year,     CAST(IF(EXTRACT(MONTH FROM date) >= 10,     DATE(EXTRACT(YEAR FROM date), 10, 1),    DATE(EXTRACT(YEAR FROM date) - 1, 10, 1)) AS DATE) AS start_of_fiscal_year,CAST(IF(EXTRACT(MONTH FROM date) >= 10,     DATE(EXTRACT(YEAR FROM date) + 1, 9, 30),     DATE(EXTRACT(YEAR FROM date), 9, 30) ) AS DATE) AS end_of_fiscal_year,FORMAT_DATE("%Y%m", date) AS yyyymm, FORMAT_DATE("%Y%m%d", date) AS yyyymmdd, CONCAT(CAST(EXTRACT(YEAR FROM date) AS STRING), "-", FORMAT_DATE("%b", date)) AS month_desc, CONCAT(CAST(EXTRACT(YEAR FROM date) AS STRING), "-Q", CAST(EXTRACT(QUARTER FROM date) AS STRING)) AS quarter_desc, CONCAT(CAST(EXTRACT(YEAR FROM date) AS STRING), "-","W",CAST(FORMAT_DATE("%V", date) AS INT64)) AS week_desc, CASE WHEN EXTRACT(DAYOFWEEK FROM date) IN (6, 7) THEN TRUE ELSE FALSE END AS is_weekend, your_project_id.your_dataset_id.find_season(date).season season, your_project_id.your_dataset_id.find_season(date).start_of_season start_of_season , holiday_name, holiday_country, special_day_name,your_project_id.your_dataset_id.find_moon_illumination(date) moon_illumination,COUNT(date) OVER (PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ORDER BY date) AS days_in_month,IF(COUNT(date) OVER (PARTITION BY EXTRACT(YEAR FROM date) ORDER BY date) = 365, 0, 1) AS is_leap_year FROM date_range d LEFT JOIN (SELECT * FROM your_project_id.your_dataset_id.date_holidays WHERE holiday_country=UPPER("' || country || '")) dh ON dh.holiday_date=d.date LEFT JOIN your_project_id.your_dataset_id.date_special_days sd ON sd.special_day_date=d.date;';


END;

User Defined SQL Procedure