CODEWITHSUNDEEP
google-bigquery
Siesta
Siesta

Reputation: 471

Sum values based on start and end dates in another table

i am trying to do a calculation in BigQuery using SQL and I have no idea about how to go about this. I have two tables:

Tablename: Periods
id            INTEGER
start_date    DATE
end_date      DATE

And another table

Tablename: Data
date         DATE
id           INTEGER
value        FLOAT

And what I want to do is to create a Query that can sum together the value for each id, and timerange (start_date to end_date) in the Periods table. In this case the Data table can have values for id's that are outside of the timerange in the Periods table so I need the Query to limit the summation to just from - to the start_date and end_date.

Hope someone can point me in the right direction on this.

Upvotes: 1

Views: 456

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10172

Consider using subquery:

SELECT 
  id,
  (SELECT SUM(value) 
   FROM Data 
   WHERE Data.id = Periods.id
     AND Data.date >= Periods.start_date 
     AND Data.date <= Periods.end_date
  ) AS sums
FROM Periods

Upvotes: 1

Related Questions