CODEWITHSUNDEEP
google-bigquery
user2668224
user2668224

Reputation: 1

trailing twelve month aggregations in google-bigquery

how do I aggregate a trailing twelve month (TTM) total use CASE statements in the WHERE clause? google-bigquery

Here is my query...

SELECT
  clientid,
  clientname,
  year(revrecdate)*100 + month(revrecdate) as Period, *** NAMES the TTM period
  count(revrecdate) as Jobs,
  sum(profit) as Profits
FROM 
  oiafd.JobProfit
WHERE
  revrecdate >= '2010-12-01 00:00:00'
  and clientid = '2KOOLPDX'
  and CASE  WHEN month(revrecdate) = 1
            THEN(revrecdate <= timestamp(concat(string(year(revrecdate)),'-01-31')) and 
              revrecdate >= timestamp(concat(string(year(revrecdate)-1),'-02-01')))
            WHEN month(revrecdate) = 2
            THEN(revrecdate <= timestamp(concat(string(year(revrecdate)),'-02-28')) and 
              revrecdate >= timestamp(concat(string(year(revrecdate)-1),'-03-01')))

Upvotes: 0

Views: 320

Answers (1)

Michael Sheldon
Michael Sheldon

Reputation: 2057

To answer the question about translating the idea of a CASE statement into something that will work with BigQuery: you can use IF functions to break each of the cases out to separate clauses in the WHERE.

Something like this will work:

...
WHERE
  revrecdate >= '2010-12-01 00:00:00'
  and clientid = '2KOOLPDX'
  and IF(month(revrecdate) = 1,
         revrecdate <= timestamp(concat(string(year(revrecdate)),'-01-31')) and 
         revrecdate >= timestamp(concat(string(year(revrecdate)-1),'-02-01')),
         true)
  and IF(month(revrecdate) = 2,
         revrecdate <= timestamp(concat(string(year(revrecdate)),'-02-28')) and 
         revrecdate >= timestamp(concat(string(year(revrecdate)-1),'-03-01')),
         true)

See https://developers.google.com/bigquery/query-reference#otherfunctions for details on IF().

Upvotes: 1

Related Questions