CODEWITHSUNDEEP
sqlloopsforeachgoogle-sheetsgoogle-bigquery
Jeff
Jeff

Reputation: 161

SQL Loop to Generate Many Rows from Various Start and End Dates

I need help with writing some SQL code and all the answers I am finding seem to assume static data and set start and end dates that need left joins. This is different.

Setup

I have a table in google sheets that is essentially

Client, Campaign, Start_Date, End_Date, Daily_Budget

Client 1, Campaign 1, 2017-01-02, 2017-01-12, 10.00

Client 2, Campaign 1, 2017-01-03, 2017-02-15, 34.09

This list is always growing with new clients, new campaigns, etc.

What I Need to Do

I want to take this and output to Bigquery using SQL (I have the connecting and transferring parts down) so that the end result is

Client 1, Campaign 1, 2017-01-02, 10.00

Client 1, Campaign 1, 2017-01-03, 10.00

Client 1, Campaign 1, 2017-01-04, 10.00

Client 1, Campaign 1, 2017-01-05, 10.00

AND SO ON

Client 2, Campaign 1, 2017-01-03, 34.09

Client 2, Campaign 1, 2017-01-04, 34.09

Client 2, Campaign 1, 2017-01-05, 34.09

Client 2, Campaign 1, 2017-01-06, 34.09

AND SO ON

Basically it loops through for every new line, creates all the necessary new rows for each date (That is what I need help with, how to generate rows off the data)

Any help would be greatly appreciated!

Upvotes: 2

Views: 324

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
SELECT Client, Campaign, Day, Daily_Budget
FROM `project.dataset.table`, 
UNNEST(GENERATE_DATE_ARRAY(Start_Date, End_Date)) Day

You can test / play with above using dummy data from your question 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Client 1' Client, 'Campaign 1' Campaign, DATE '2017-01-02' Start_Date, DATE '2017-01-12' End_Date, 10.00 Daily_Budget UNION ALL
  SELECT 'Client 2', 'Campaign 1', DATE '2017-01-03', DATE '2017-02-15', 34.09 
)
SELECT Client, Campaign, Day, Daily_Budget
FROM `project.dataset.table`, 
UNNEST(GENERATE_DATE_ARRAY(Start_Date, End_Date)) Day

Upvotes: 2

Related Questions