CODEWITHSUNDEEP
sqlgoogle-bigquery
veena989798
veena989798

Reputation: 31

How to move variables containing date in inner subquery to outer subquery in bigquery

The following query works well, displaying 2018th year all mondays in each month.

select dt3 from unnest(generate_date_array(date_trunc('2018-1-1',week(monday)),'2019-1-1',interval 1 week))dt3

But in the above query I want to replace date_trunc('2018-1-1',week(monday)) with start_date and '2019-1-1',interval 1 week with end_date. So I wrote the following query, but it didn't worked. Any suggestion.

select dt2 from unnest(generate_date_array(start_date,end_date))dt2 from(select date_add(start_date,interval 371 day)end_date,start_date from (select date_trunc('2018-1-1',week(monday))start_date))

Upvotes: 0

Views: 109

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

#standardSQL
WITH dates AS (
  SELECT 1 seq, DATE_TRUNC('2018-1-1',week(monday)) start_date, DATE '2019-1-1' end_date 
)
SELECT dt2 
FROM dates, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 WEEK)) dt2

In case if you want to only start_date (as in second query in your question): 

#standardSQL
WITH dates AS (
  SELECT 1 seq, DATE_TRUNC('2018-1-1',week(monday)) start_date 
)
SELECT dt2 
FROM dates, UNNEST(GENERATE_DATE_ARRAY(start_date, DATE_ADD(start_date,INTERVAL 371 DAY), INTERVAL 1 WEEK)) dt2

Upvotes: 1

Related Questions