Home > front end >  Creating a list of dates within WITH clause - GG Big Query
Creating a list of dates within WITH clause - GG Big Query

Time:10-18

I want to to create a list of every first day of every month starting from 2021-01-01 for the next 24 months.

With normal SQL, it has worked with these following codes that I had found online:

with mnt as (
  select 1 as n, convert(date,'20210101',112) as d
  union all
  select n   1, dateadd(month,1,d)
  from mnt
  where n < 24
)
select d from mnt

(You can also see it in dbfiddle)

However, when I tried that same logic on BigQuery it did not work, as the error stated that: Table "mnt" must be qualified with a dataset (e.g. dataset.table)

with mnt as (
  select 1 as n, CAST('2021-07-01' AS DATE FORMAT 'YYYY-MM-DD') as d
  union all
  select n 1, date_add(CAST('2021-07-01' AS DATE FORMAT 'YYYY-MM-DD'), INTERVAL 1 MONTH)
  from mnt
  where n < 24
)

select d as salesmonth from mnt

See the code on BigQuery

I also tried looping on Big Query

declare x DATE DEFAULT "2021-07-01";

REPEAT 
  Set x = date_add(x, INTERVAL 1 MONTH);
  SELECT x;
  until x > CAST('2023-07-01' AS DATE FORMAT 'YYYY-MM-DD')
END REPEAT;

but the results do not come out in tabular form, nor I know how to put them within a WITH clause.

Does anyone know how to fix this and adapt the above SQL query in Big Query?

CodePudding user response:

Below query using WITH RECURSIVE will work in your case.

with recursive mnt as (
  select 1 as n, DATE '2021-01-01' as d
  union all
  select n   1, date_add(d, INTERVAL 1 MONTH)
  from mnt
  where n < 24
)
select d as salesmonth from mnt;

But in BigQuery GENERATE_DATE_ARRAY() is more handy than recursive approach.

SELECT * 
  FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2022-12-01', INTERVAL 1 MONTH)) salesmonth

CodePudding user response:

Consider also below option

select date_add('2021-01-01', interval i month)
from unnest(generate_array(0, 23)) i
  • Related