4

I'm attempting to create a date view using the MIN and MAX of dates from another table in Google Bigquery. I've come across GENERATE_DATE_ARRAY and have been able to create the dates I'm after in a single array. However, splitting them out to a single date per row has been the hard part.

I've attempted to use UNNEST to do this but when I run the code it only gives me the first result so I'm missing something here.

This is the code I've used to generate the array, but I'm stuck on getting it to split out:

SELECT GENERATE_DATE_ARRAY( 
              MIN(CAST(journaldate AS DATE)), 
              MAX(CAST(journaldate AS DATE))
            )
          AS Period
FROM
dataset.table
jbev
  • 83
  • 1
  • 5

2 Answers2

8

I think you want:

SELECT Period
FROM (SELECT MIN(CAST(journaldate AS DATE)) as min_date,
             MAX(CAST(journaldate AS DATE)) as max_date
      FROM dataset.table
     ) t JOIN
     UNNEST(GENERATE_DATE_ARRAY(t.min_date, t.max_date)) period
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Great thanks. This works fine. I was originally trying to unnest the whole statement but joining to an the UNNEST array is the trick. Thanks for your help. – jbev Jun 08 '19 at 23:26
1

Another version is: 

#standardSQL
SELECT period FROM UNNEST(
  GENERATE_DATE_ARRAY(
    (SELECT MIN(CAST(journaldate AS DATE)) FROM `project.dataset.table`),
    (SELECT MAX(CAST(journaldate AS DATE)) FROM `project.dataset.table`)
  )
) period
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Thank you as well. This also works, it was 0.1 of sec slower but that could of just been my connection at the time :). 0.1 of sec is nothing amongst friends. – jbev Jun 08 '19 at 23:27