DROP FUNCTION dbo.FN_CourseCredits
GO

CREATE FUNCTION dbo.FN_CourseCredits ( @CourseID INT )
RETURNS Table
AS
RETURN	(

SELECT	A.* -- Return only the recordset from courserecords
	FROM	(
		SELECT * -- This select gets ALL course records withing a group.  A user MIGHT have more than 1
			FROM	dbo.CourseRecords CR
			WHERE	CR.CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID))
		) A
	INNER JOIN ( -- Get the earliest course record for each user in this credit group
		SELECT	UserID, MIN(CRecordFinish) as CRecordFinish
			FROM	dbo.CourseRecords CR
			INNER JOIN dbo.Courses C ON C.CourseID = CR.CourseID
			WHERE	CR.CRecordFinish > C.CourseStartDate
			AND	CR.CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID))
			GROUP BY UserID
	) B ON B.UserID = A.UserID AND B.CRecordFinish = A.CRecordFinish

	)
GO