SQL Query to Find if (Count of date > X) = 0 For Group of ID

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.

I have the following query built to return a list of chemicals and other related fields.

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] >= DATEADD(day,-60, GETDATE())
ORDER BY RDB.Chemical_Record.[Chemical_ID], RDB.Chemical_Record.[Expires_Date], RDB.Assay_Group.[Assay_Group_Name]

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.

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 Count, Group and Having, but since I’ve never worked with those I can’t get them to work for me. Example:

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] > DATEADD(day,30,GETDATE())
GROUP BY RDB.Chemical_Record.[Chemical_ID], RDB.Chemical_Record.[Expires_Date]
ORDER BY RDB.Chemical_Record.[Chemical_ID]

As you can see from this example, it doesn’t return the count of ID’s where Expiration Date > DATEADD(day,30,GETDATE()) nor does it return the ID’s that I actually wanted.

What I need to return is all chemicals (ID) that DO NOT have an expiration date > 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 > Today + 30. All the other ID’s should not show up since they DO have ID’s of COUNT(Expiration Date > Today + 30) > 0.

If someone could help me build the query using the appropriate Aggregate functions, it would be MUCH appreciated.

Answer

What I need to return is all chemicals (ID) that DO NOT have an expiration date > Today + 30 for that specific ID.

For this question, you can use a HAVING clause. No WHERE is needed:

SELECT COUNT(*) as Count_ID, cr.[Chemical_ID]
FROM RDB.Chemical_Record cr
GROUP BY cr.[Chemical_ID]
HAVING MAX(cr.Expires_Date) <= DATEADD(day, 30, GETDATE())
ORDER BY cr.[Chemical_ID]

Advertisement

Answer