title: Use DataStudio to specify the date range for a custom query in BigQuery, where the date range influences operators in the query
body: <p>I currently have a DataStudio dashboard connected to a BigQuery custom query. </p>
  
  <p>That BQ query has a hardcoded date range and the status of one of the columns (<strong><code>New_or_Relicensed</code></strong>) can change dynamically for a row, based on the dates specified in the range. I would like to be able to alter that range from DataStudio. </p>
  
  <p>I have tried: </p>
  
  <ul>
  <li><p>simply connecting the DS dashboard to the custom query in BQ and then introducing a date range filter, but as you can imagine - that does not work because it's operating on an already hard-coded date range. </p></li>
  <li><p>reviewing similar answers, but their problem doesn't appear to be quite the same E.g. <a href="https://stackoverflow.com/questions/41114586/bigquery-data-studio-custom-query">BigQuery Data Studio Custom Query</a></p></li>
  </ul>
  
  <p>Here is the query I have in BQ: </p>
  
  <pre><code>SELECT t0.New_Or_Relicensed, t0.Title_Category FROM (WITH
    report_range AS
    (
      SELECT
        TIMESTAMP '2019-06-24 00:00:00' AS start_date,
        TIMESTAMP '2019-06-30 00:00:00' AS end_date
    )
  SELECT
    schedules.schedule_entry_id AS Schedule_Entry_ID,
    schedules.schedule_entry_starts_at AS Put_Up,
    schedules.schedule_entry_ends_at AS Take_Down,
    schedule_entries_metadata.contract AS Schedule_Entry_Contract,
    schedules.platform_id AS Platform_ID,
    platforms.platform_name AS Platform_Name,
    titles_metadata.title_id AS Title_ID,
    titles_metadata.name AS Title_Name,
    titles_metadata.category AS Title_Category,
    IF (other_schedules.schedule_entry_id IS NULL, "new", "relicensed") AS New_Or_Relicensed
  FROM
    report_range, client.schedule_entries AS schedules
  JOIN client.schedule_entries_metadata 
    ON schedule_entries_metadata.schedule_entry_id = schedules.schedule_entry_id 
  JOIN
    client.platforms
    ON schedules.platform_id = platforms.platform_id
  JOIN
    client.titles_metadata
    ON schedules.title_id = titles_metadata.title_id
  LEFT OUTER JOIN
    client.schedule_entries AS other_schedules
    ON schedules.platform_id = other_schedules.platform_id
    AND other_schedules.schedule_entry_ends_at &lt; report_range.start_date
    AND schedules.title_id = other_schedules.title_id
  WHERE
    ((schedules.schedule_entry_starts_at &gt;= report_range.start_date AND
     schedules.schedule_entry_starts_at &lt;= report_range.end_date) OR
    (schedules.schedule_entry_ends_at &gt;= report_range.start_date AND
     schedules.schedule_entry_ends_at &lt;= report_range.end_date))
  ) AS t0 LIMIT 100; 
  </code></pre>
  
  <p>Essentially - I would like to be able to set the <code>start_date</code> and <code>end_date</code> from google data studio, and have those dates incorporated into the <code>report_range</code> that then influences the operations in the rest of the query (that assign a schedule entry as <code>new</code> or <code>relicensed</code>).</p>