title: SQL Query to Find if (Count of date > X) = 0 For Group of ID
body: <p>I apologize if the title is not be correct as I'm not sure what I need to ask for, since I don't know how to build the query.</p>
  <p>I have the following query built to return a list of chemicals and other related fields.</p>
  <pre><code>SELECT DISTINCT
      RDB.Chemical_Record.[Chemical_ID], 
      RDB.Chemical_Record.[Expires_Date], 
      RDB.Assay_Group.[Assay_Group_Name] AS [Assay Group], 
      RDB.Chemical.[Chemical_Name], 
      RDB.Chemical.[Product_Number], 
      RDB.Chemical_Record.[Lot_Number], 
      RDB.Storage_Location.[Location_Name]
  FROM RDB.Chemical_Record 
      LEFT JOIN RDB.Chemical ON Chemical_Record.[Chemical_ID] = Chemical.[ID_Chemical]
      LEFT JOIN RDB.Storage_Location ON Storage_Location.[ID_Storage_Location] = Chemical_Record.[Storage_Location_ID]
      LEFT JOIN RDB.Chemical_To_AGroup ON Chemical_To_AGroup.[Chemical_ID] = Chemical_Record.[Chemical_ID]
      LEFT JOIN RDB.Assay_Group ON Assay_Group.[ID_Assay_Group] = Chemical_To_AGroup.[Assay_Group_ID]
  WHERE RDB.Chemical_Record.[Expires_Date] &gt;= DATEADD(day,-60, GETDATE())
  ORDER BY RDB.Chemical_Record.[Chemical_ID], RDB.Chemical_Record.[Expires_Date], RDB.Assay_Group.[Assay_Group_Name]
  </code></pre>
  <p>I am using this query in a VB.Net application where it exports the results to an Excel worksheet and then performs additional actions to delete the rows I don't need.  The process to query is quick, but working with Excel from .Net is painful and slow.</p>
  <p>Instead I'd like to build the query to return the exact results I want, which I think is possible, I just can't figure out how. I have tried using a combination of <code>Count</code>, <code>Group</code> and <code>Having</code>, but since I've never worked with those I can't get them to work for me.
  Example:</p>
  <pre><code>SELECT
      COUNT(RDB.Chemical_Record.[Chemical_ID]) Count_ID, 
      RDB.Chemical_Record.[Chemical_ID],
      RDB.Chemical_Record.[Expires_Date]
  FROM RDB.Chemical_Record 
  WHERE RDB.Chemical_Record.[Expires_Date] &gt; DATEADD(day,30,GETDATE())
  GROUP BY RDB.Chemical_Record.[Chemical_ID], RDB.Chemical_Record.[Expires_Date]
  ORDER BY RDB.Chemical_Record.[Chemical_ID]
  </code></pre>
  <p><a href="https://i.stack.imgur.com/SjUEY.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SjUEY.jpg" alt="enter image description here" /></a></p>
  <p>As you can see from this example, it doesn't return the count of ID's where Expiration Date &gt; <code>DATEADD(day,30,GETDATE())</code> nor does it return the ID's that I actually wanted.</p>
  <p>What I need to return is all chemicals (ID) that DO NOT have an expiration date &gt; Today + 30 for that specific ID.  The screenshot below shows an example of the data that gets pulled.  The yellow highlighted rows are the only two in that set that should get returned as there are no other chemicals of those two ID's with an expiration date &gt; Today + 30.  All the other ID's should not show up since they DO have ID's of <code>COUNT</code>(Expiration Date &gt; Today + 30) &gt; 0.</p>
  <p><a href="https://i.stack.imgur.com/UF2me.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UF2me.jpg" alt="enter image description here" /></a></p>
  <p>If someone could help me build the query using the appropriate Aggregate functions, it would be MUCH appreciated.</p>