Need help with nested SQL for a stored procedure

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,
	dbo.sessiontable st
    WHERE   b.BatchID = st.BatchID
	AND MAX(st.EndTime) < DATEADD(mi,-10,GETDATE())      
                 AND b.JobName = 'JOB1' --
    ORDER BY b.Priority , b.BatchID

Open in new window


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.
jasonkruegerAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
For starters
Change the reference to sessiontable to a subquery that does the MAX.
Any time you want to filter based on an aggregate like MAX, that's done in HAVING and not WHERE
SELECT b.* 
FROM  dbo.batchtable b
	JOIN (
		SELECT id, MAX(EndTime)
		FROM dbo.sessiontable 
		GROUP BY id
		HAVING MAX(EndTime) < DATEADD(mi,-10,GETDATE()) ) st ON b.BatchID = st.BatchID
WHERE b.JobName = 'JOB1'
ORDER BY b.Priority, b.BatchID

Open in new window

0
 
SharathData EngineerCommented:
try this.
    SELECT  *
    FROM    dbo.batchtable b,
	dbo.sessiontable st
    WHERE   b.BatchID = st.BatchID
	AND st.EndTime = (SELECT MAX(st1.EndTime) FROM sessiontable st1 WHERE st1.EndTime > DATEADD(mi,-10,GETDATE())
	AND st.BatchID = st1.BatchID)
	AND b.JobName = 'JOB1' --
    ORDER BY b.Priority , b.BatchID  

Open in new window

0
 
jasonkruegerAuthor Commented:
Thanks Jim! worked like a charm
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.