CODEWITHSUNDEEP
sqlgoogle-bigquery
J. Ayo
J. Ayo

Reputation: 560

UNION ALL to add in missing dates to a table

I currently use a UNION ALL to add in a row to my Big Query table based on the current_date - 1 to ensure that a row exists for 'yesterday':

SELECT
  RowDate,
  COUNT(*) AS Records
FROM
  table1
GROUP BY
  RowDate UNION ALL
SELECT
  DATE_SUB(CURRENT_DATE(),
    INTERVAL 1 DAY) AS RowDate,
  0 AS Records
ORDER BY
  RowDate DESC

What I would like to do is add a row for every date within a range of dates. Is this possible within a view query, without setting up a separate table of dates?

Any help much appreciated

Upvotes: 0

Views: 369

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Yes, you can use GENERATE_DATE_ARRAY function for this

For example: 

GENERATE_DATE_ARRAY('2018-01-01', '2018-01-31') AS RowDate

so, in your specific case it will be something like below 

UNION ALL
SELECT RowDate, 0 AS Records
FROM UNNEST(GENERATE_DATE_ARRAY('2018-01-01', '2018-01-31')) AS RowDate

Upvotes: 2

Related Questions