Is there a way to expand a date range into rows in bigquery where you have historic data already present in the dataset?

Question

For eg. Itemid start_date end_date A. 2021-07-02. 2021-09-01 B. 2021-06-01. 2021-06-03

Output. Itemid date A. 2021-07-02 A. 2021-07-03 and so on and then B. 2021-06-01 B. 2021-06-02

Answer 1

You can create a view using this at bigquery and name it like v_date_dimension:

SELECT
  FORMAT_DATE('%F', d) as id,
  d AS full_date,
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(WEEK FROM d) AS year_week,
  EXTRACT(DAY FROM d) AS year_day,
  EXTRACT(YEAR FROM d) AS fiscal_year,
  FORMAT_DATE('%Q', d) as fiscal_qtr,
  EXTRACT(MONTH FROM d) AS month,
  FORMAT_DATE('%B', d) as month_name,
  FORMAT_DATE('%w', d) AS week_day,
  FORMAT_DATE('%A', d) AS day_name,
  (CASE WHEN FORMAT_DATE('%A', d) IN ('Sunday', 'Saturday') THEN 0 ELSE 1 END) AS day_is_weekday,
FROM (
  SELECT
    *
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2014-01-01', '2050-01-01', INTERVAL 1 DAY)) AS d )

---

After that you can do a query like :

select * from v_date_dimension D
left join YOUR_TABLE tbl ON D.full_date between tbl.start_date and tbl.end_date