title: DATE RANGE NOT BETWEEN two dates from another table
body: <p>I have two tables related to each other [t1] &amp; [t2] with two columns: [id]
   &amp; [date].  I am hoping to retrieve the [id] and [date] from [t1] where no [t2].[date] falls on or one year prior to the MIN([t1].[date]) .</p>
  
  <p>Basically excluding [id]s that have a [t2].[date] (any date occurrence - dynamic) 'on or one year prior to the earliest [t1].[date]' .  Any [t2].[id]s before one year from MIN([t1].[date]) are ok, and any [t2].[id]s after MIN([t1].[date]) are good as well.  If any [t2].[date] value fall in the one year range, that [id] should be excluded from the returned data.</p>
  
  <p>Conceptually I want something like: </p>
  
  <pre><code>SELECT [id]
  FROM [t1]
  WHERE [t2].[date] NOT BETWEEN DATEADD(YEAR, -1, [t1].[date]) AND [t1].[date]
  </code></pre>
  
  <p>But I'm struggling with how to approach this; use a JOIN/UNION, DECLARE variables, a WHERE clause.  Any help would be appreciated!  Thank you!</p>
  
  <pre><code>/* Adding this new information below: */
  </code></pre>
  
  <p>I like the response by Scott; however, I don't think I made my 'conceptual code' coherent enough originally.  I think I'm looking for something more like this, but I am returning an error (An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.):</p>
  
  <pre><code>SELECT [id]
  FROM [t1]
  WHERE NOT EXISTS
    (SELECT * FROM [t2] WHERE [t2].[date]
      BETWEEN DATEADD(YEAR, -1, MIN([t1].[date])) AND MIN([t1].[date]))
  
   /* Adding this new information below: */
  </code></pre>
  
  <p>I believe I have found the solution to original inquiry.  Please give an 'Arrow Up' if you like it!</p>
  
  <pre><code>SELECT DISTINCT [t1].[id]
  FROM [t1]
  WHERE NOT EXISTS (SELECT 1
                    FROM [t2]
                    WHERE [t2].[id] = [t1].[id]
                    AND [t2].[date] BETWEEN DATEADD(dd, -365, [t1].[date])
                                            AND [t1].[date])
  </code></pre>