I have 2 tables I need to include in this query.
1. Batch (each record is a batch)
2. Session (could have multiple sessions per batch)
The problem I'm having is that I need to only return batches that the most recent 'EndTime' in the session table for the batch is older than 10 minutes.
Here's how I tried to do it, but I'm getting this 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.
SELECT b.* ,
FROM dbo.batchtable b,
WHERE b.BatchID = st.BatchID
AND MAX(st.EndTime) < DATEADD(mi,-10,GETDATE())
AND b.JobName = 'JOB1' --
ORDER BY b.Priority , b.BatchID
I'm guessing this will be a simple solution for the experts. I don't work in SQL enough to remember all the syntax and how things need to be grouped.
Any help is appreciated.