SELECT
			s.student_id, 
			s.LastName, 
			s.FirstName, 
			s.MiddleName,
			d.department,
			MIN(b.start_dt) AS start_dt, 
			MAX(b.end_dt) AS end_dt
		INTO #TMPQUERY1
		FROM         
		(SELECT     student_id, LastName, FirstName, MiddleName
		FROM          dbo.Student) AS s 
		INNER JOIN
		(SELECT     student_id, block_id, assignment_id
		FROM          dbo.Assignments
		WHERE  status = 'approved') AS a 
		ON s.student_id = a.student_id 
		INNER JOIN
		(SELECT     block_id, elective_id, start_dt, end_dt
		FROM          dbo.Block_Schedule
		WHERE      (end_dt > GETDATE()) AND (start_dt <= GETDATE() + 14)) AS b 
		ON a.block_id = b.block_id 
		INNER JOIN
		(SELECT     elective_id, dept_id
		FROM          dbo.Electives) AS e 
		ON b.elective_id = e.elective_id 
		INNER JOIN
		(SELECT     Dept_id, name AS department
		FROM          dbo.Departments) AS d 
		ON e.dept_id = d.Dept_id
		GROUP BY s.student_id, s.LastName, s.FirstName, s.MiddleName, d.department

		SELECT T1.* FROM #TMPQUERY1 T1
			WHERE T1.START_DT = (SELECT MIN(START_DT) FROM #TMPQUERY1
					WHERE T1.student_id=student_id GROUP BY student_id)
		ORDER BY LASTNAME

		DROP TABLE #TMPQUERY1

SELECT     TOP (100) PERCENT student_id AS pk_student_id, LastName, FirstName, MiddleName, department,start_dt, 
                      end_dt
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY s.student_id
                       ORDER BY b.start_dt) AS SEQ, s.student_id, s.LastName, s.FirstName, s.MiddleName, d .department, MIN(b.start_dt) 
                      AS start_dt, MAX(b.end_dt) AS end_dt
FROM         (SELECT     student_id, LastName, FirstName, MiddleName
                       FROM          dbo.Student) AS s INNER JOIN
                          (SELECT     student_id, block_id, assignment_id
                            FROM          dbo.Assignments
                            WHERE      status = 'approved') AS a ON s.student_id = a.student_id INNER JOIN
                          (SELECT     block_id, elective_id, start_dt, end_dt
                            FROM          dbo.Block_Schedule
                            WHERE      (end_dt > GETDATE()) AND (start_dt <= GETDATE() + 14)) AS b ON a.block_id = b.block_id INNER JOIN
                          (SELECT     elective_id, dept_id
                            FROM          dbo.Electives) AS e ON b.elective_id = e.elective_id INNER JOIN
                          (SELECT     Dept_id, name AS department
                            FROM          dbo.Departments) AS d ON e.dept_id = d .Dept_id
GROUP BY s.student_id, s.LastName, s.FirstName, s.MiddleName,b.start_dt, b.end_dt, d .department) AS T
WHERE     SEQ = 1

quote:

---

Originally posted by mikecro

Result:

The current compatibility level is 90.

---

quote:

---

Originally posted by mikecro

I'm using the SSMS, which I believe incorporates query analyzer in the editor.

---

quote:

---

Originally posted by mikecro

Thanks again for your help. It does run and the results are correct, but SqlServer doesn't like it for some reason.

---