title: SQL using select subquery for date range in where statement to determine the max value in that date range
body: <p>I am trying to get a the max value per user within a time frame.  The date range is determined by the first occurrence by each user within a separate time range and the 2 weeks prior,  i.e. the first time a user shows up in October is 2018-10-01, therefore the range is from 2018-09-18 to 2018-10-01.  Within that range, I am looking for the maximum Value of a column. All of this data comes from the same table.</p>
  
  <p>Example:    </p>
  
  <pre class="lang-none prettyprint-override"><code>+------------+------------+---------+
  | Profile_ID |    Date    | Value   |
  +------------+------------+---------+
  |          1 | 2018-10-05 |    100  |
  |          2 | 2018-10-02 |     50  |
  |          2 | 2018-10-04 |     78  |
  |          2 | 2018-10-05 |     56  |
  |          1 | 2018-10-08 |    110  |
  |          1 | 2018-10-01 |     99  |
  |          2 | 2018-09-30 |     88  |
  |          1 | 2018-09-27 |    106  |
  +------------+------------+---------+
  </code></pre>
  
  <p>I am looking for the peak VALUE from the two weeks prior to and including the first occurrence after OCT 1.  That would be 2018-10-01 for USER 1 and 2018-10-02 for user 2.  The peak value would then be 106 and 88, respectively.</p>
  
  <p>I tried to the code:</p>
  
  <pre><code>SELECT max(Value)
  FROM table
  WHERE Date BETWEEN (
    SELECT (min(Date) - INTERVAL 2 week)
    FROM table
    WHERE stamp between '2018-10-01' AND '2018-10-25'
    GROUP BY profile_id
    )
    AND
    (
    SELECT min(Date)
    FROM table
    WHERE stamp between '2018-10-01' AND '2018-10-25'
    GROUP BY profile_id
    )
  </code></pre>
  
  <p>I am getting an error:</p>
  
  <blockquote>
    <p>Syntax Error: at or near "2"</p>
  </blockquote>
  
  <p>but I think that is just the start of my problems.  Does anyone know the best way to find the max value in that time range for each profile_id?</p>