CODEWITHSUNDEEP
sqlgoogle-bigquery
Cristian Avendaño
Cristian Avendaño

Reputation: 477

Get last 6 months first day in BigQuery

I have a partitioned table by a date, in the field LOAD_DATE. I need to extract the last 6 partitions for the first day of each month.

i.e.
2022-01-01
2021-12-01
2021-11-01
2021-10-01
2021-09-01
2021-08-01

So far, I have something like this, which is pretty ugly, because I have to copy-paste a line to allow more months:

SELECT *
FROM partitionedTable
where FECHA_CARGA IN (
              Select 
              DATE_TRUNC(CURRENT_DATE, MONTH)
              UNION ALL 
              Select 
              DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH), MONTH)
              UNION ALL 
              Select 
              DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 2 MONTH), MONTH),
              UNION ALL 
              Select 
              DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH), MONTH),
              UNION ALL 
              Select 
              DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 4 MONTH), MONTH),
              UNION ALL 
              Select 
              DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 5 MONTH), MONTH)
)

How can I improve this code?

Upvotes: 1

Views: 1638

Answers (1)

Daniel Zagales
Daniel Zagales

Reputation: 3034

You should be able to change your where clause to

fecha_carga in UNNEST(generate_date_array(  date_trunc(date_sub(current_date(), INTERVAL 5 MONTH), MONTH), current_date() , INTERVAL 1 MONTH))

Upvotes: 1

Related Questions