CODEWITHSUNDEEP
google-bigquery
E liquid Vape
E liquid Vape

Reputation: 441

Get data from last year

I am trying to get data from 1 year ago from the given date in bigquery. date(DATE_ADD(date(DATE_ADD(timestamp(New_date),(DATEDIFF(timestamp(New_date), CURRENT_DATE())-1), "DAY")), -354, "DAY"))

The query above works, but when I try to put it in a case statement I get null.

sum(case when date(New_day) = date(DATE_ADD(date(DATE_ADD(timestamp(New_date),(DATEDIFF(timestamp(New_date), CURRENT_DATE())-1), "DAY")), -354, "DAY")) then 'this is working' else null end) as lastyeardata

How do I get data for 1 year ago on the same day to do a YoY comparison?

Edit @Mikhail's suggestions: #standardSQl WITH `yourTable` AS ( SELECT 1 AS id, '2017-08-14' AS New_date, '1234' AS volume UNION ALL SELECT 2 AS id, '2017-08-13' AS New_date, '2345' AS volume UNION ALL SELECT 3 AS id, '2017-08-14' AS New_date, '3456' AS volume UNION ALL SELECT 4 AS id, '2017-08-14' AS New_date, '4567' AS volume UNION ALL SELECT 5 AS id, '2016-08-14' AS New_date, '5678' AS volume UNION ALL SELECT 6 AS id, '2016-08-13' AS New_date, '6789' AS volume UNION ALL SELECT 7 AS id, '2016-08-12' AS New_date, '6789' AS volume UNION ALL SELECT 8 AS id, '2016-08-11' AS New_date, '1011' AS volume ) select New_date ,volume as thisyeardata ,Case WHEN PARSE_DATE('%Y-%m-%d', New_date) = DATE_ADD(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL -1 YEAR) THEN volume ELSE NULL end as lastyearvolume ,DATE_ADD(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL -1 YEAR) as lastyear FROM `yourTable`

For some reason lastyearvolume is giving me null.

Upvotes: 0

Views: 5774

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL (which is really recommended by BigQuery team to use versus Legacy one) 

#standardSQl
WITH `yourTable` AS (
  SELECT 1 AS id, '2017-08-14' AS New_date, '2016-08-14' AS New_day UNION ALL
  SELECT 2 AS id, '2017-08-13' AS New_date, '2016-08-13' AS New_day UNION ALL
  SELECT 3 AS id, '2017-08-14' AS New_date, '2016-08-12' AS New_day UNION ALL
  SELECT 4 AS id, '2017-08-14' AS New_date, '2016-08-11' AS New_day 

)
SELECT 
  COUNT(
    CASE 
      WHEN PARSE_DATE('%Y-%m-%d', New_day) = DATE_ADD(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL -1 YEAR) 
      THEN 'this is working' 
      ELSE NULL 
    END
  ) AS lastyeardata
FROM `yourTable`

As you would expect two rows are just counted as they have New_date and New_day year apart, the rest are not

Above example assumes your dates fields are of STRING type If they are of Date Type - you just omit PARSE_DATE function

DATE_ADD has counterpart - DATE_SUB - so it can be used instead as DATE_SUB(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL 1 YEAR)

Update based on your updated example:

simple and fast way to adjust your query t work: 

#standardSQl
WITH `yourTable` AS (
SELECT 1 AS id, '2017-08-13' AS New_date, '1234' AS volume UNION ALL
SELECT 2 AS id, '2017-08-14' AS New_date, '2345' AS volume UNION ALL
SELECT 3 AS id, '2017-08-15' AS New_date, '3456' AS volume UNION ALL
SELECT 4 AS id, '2017-08-16' AS New_date, '4567' AS volume UNION ALL
SELECT 5 AS id, '2016-08-13' AS New_date, '5678' AS volume UNION ALL
SELECT 6 AS id, '2016-08-14' AS New_date, '6789' AS volume UNION ALL
SELECT 7 AS id, '2016-08-15' AS New_date, '6789' AS volume UNION ALL
SELECT 8 AS id, '2016-08-16' AS New_date, '1011' AS volume
)
SELECT 
  this_year.New_date
  ,this_year.volume AS thisyeardata
  ,last_year.volume AS lastyeardata
FROM `yourTable` AS this_year
JOIN `yourTable` AS last_year
ON PARSE_DATE('%Y-%m-%d', last_year.New_date) = DATE_ADD(PARSE_DATE('%Y-%m-%d', this_year.New_date), INTERVAL -1 YEAR)

Upvotes: 2

Related Questions