Heh... it's almost a shame that you figured it out on your own because, except for a couple of foreign key errors, you did such a nice job posting the test data from the problem.

Just in case anyone else wants to play a bit with this problem, here's the corrected test data setup and the solution that pietlinden came up with.

/********************

drop table [Prereq]

drop table [Course]

********************/

go

CREATE TABLE [dbo].[Course](

[CourseID] [char](7) NOT NULL,

CONSTRAINT [pk_Course] PRIMARY KEY CLUSTERED

(

[CourseID] ASC

)

)

/****** Object: Table [dbo].[Prereq] Script Date: 8/24/2013 9:40:18 PM ******/

CREATE TABLE [dbo].[Prereq](

[NextCourseID] [char](7) NOT NULL,

[ReqCourseID] [char](7) NOT NULL,

CONSTRAINT [pk_Prereq] PRIMARY KEY CLUSTERED

(

[NextCourseID] ASC,

[ReqCourseID] ASC

)

)

GO

ALTER TABLE [dbo].[Prereq] WITH CHECK ADD FOREIGN KEY([NextCourseID])

REFERENCES [dbo].[Course] ([CourseID])

GO

ALTER TABLE [dbo].[Prereq] WITH CHECK ADD FOREIGN KEY([ReqCourseID])

REFERENCES [dbo].[Course] ([CourseID])

GO

/* Courses */

INSERT INTO Course (CourseID) VALUES ('503');

INSERT INTO Course (CourseID) VALUES ('515'); -- no prereqs

INSERT INTO Course (CourseID) VALUES ('601'); -- no prereqs

INSERT INTO Course (CourseID) VALUES ('604');

INSERT INTO Course (CourseID) VALUES ('605');

INSERT INTO Course (CourseID) VALUES ('606');

INSERT INTO Course (CourseID) VALUES ('607'); -- no prereqs

INSERT INTO Course (CourseID) VALUES ('608');

INSERT INTO Course (CourseID) VALUES ('620');

/* Prerequisites */

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('503','515');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('604','503');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('605','503');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('608','503');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('604','601');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('620','605');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('620','608');

INSERT INTO Prereq (NextCourseID, ReqCourseID) VALUES ('606','605');

-- courses without prereqs

WITH PrereqsCTE (CourseID, Depth) AS

(

--- Anchor: courses without prerequisites

SELECT c.CourseID, 0 AS Lvl

FROM Course c

WHERE NOT EXISTS (SELECT * FROM Prereq WHERE NextCourseID = c.CourseID)

UNION ALL

-- Recursive member: courses that have other courses as prerequisites.

SELECT p.NextCourseID, Depth + 1

FROM Prereq p INNER JOIN PrereqsCTE

ON p.ReqCourseID = PrereqsCTE.CourseID

)

SELECT CourseID, Depth

FROM PrereqsCTE;