GBQ previous month

1
votes

I'm trying to pull all days for previous month however my below query only pulls last month’s same day. Is this possible? Thanks for the help!

select *
FROM
(TABLE_DATE_RANGE([BI_UU_HH.CID_BKID_AID_Raw_],
TIMESTAMP(DATE_ADD(CURRENT_DATE(),-1,"Month")),
TIMESTAMP(DATE_ADD(CURRENT_DATE(),-1,"Month"))))

Results: Date 2016-07-30

Desired Results: Whole month of July

2
google-bigquery

2 Answers

3
votes

Using standard SQL (uncheck "Use Legacy SQL" in Options):

SELECT *
FROM
`BI_UU_HH.CID_BKID_AID_Raw_*`
WHERE
  SUBSTR(_table_suffix, 0, 6) =
  FORMAT_DATE("%E4Y%m", DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH))
0
votes

Try 

SELECT *
FROM TABLE_QUERY([BI_UU_HH],
"REGEXP_MATCH(table_id, 'CID_BKID_AID_Raw_' + REPLACE(LEFT(STRING(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'MONTH')), 7), '-', ''))")

See TABLE_QUERY for more details

OR

slightly modified version (still in Legacy SQL) with use of STRFTIME_UTC_USEC function instead of string manipulation 

SELECT *
FROM TABLE_QUERY([BI_UU_HH],
"REGEXP_MATCH(table_id, 'CID_BKID_AID_Raw_' + STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'MONTH'), '%Y%m'))")