0

Using BigQuery and Legacy SQL. I am trying to modify my WHERE statement to look back 3 days if current date is a Monday, otherwise look back 1.

I cannot figure out how to do this.

Currently I am just looking to see if the date is current date -1.

Current code:

WHERE
TIMESTAMP(o.last_modified_on) > DATE_ADD(TIMESTAMP(CURRENT_DATE()), -1, 'DAY')

Any guidance would be appreciated.

Thinking something along these lines (note: this doesn't work) 

Where CASE TIMESTAMP(ii.last_modified_on) = TIMESTAMP(CURRENT_DATE()) WHEN 
DAYOFWEEK(TIMESTAMP(ii.last_modified_on)) = 2 THEN 
DATE_ADD(TIMESTAMP(CURRENT_DATE()), -3, 'DAY') ELSE 
DATE_ADD(TIMESTAMP(CURRENT_DATE()), -1, 'DAY') END
Toddbg
  • 1
  • 1
  • Mikhail's answer is a good one. I do have to ask, though: is there a reason to use legacy SQL? We advocate for use of standard SQL in BigQuery for new queries, since it generally has more features, better-defined semantics, etc. – Elliott Brossard Jan 29 '18 at 23:07

1 Answers1

1
WHEN TIMESTAMP(o.last_modified_on) > 
  CASE WHEN DAYOFWEEK(CURRENT_TIMESTAMP()) = 2 
    THEN DATE_ADD(CURRENT_TIMESTAMP(), -3, 'DAY') 
    ELSE DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY') 
  END
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230