SELECT     TOP (100) PERCENT RegisterSession.Date, StudentDetail.AcademicYearID, StudentDetail.RefNo, 
                      StudentDetail.FirstForename, StudentDetail.Surname, Offering.OfferingID, 
                      Offering.Code, Offering.Name, Register.RegisterID, Register.RegisterNo, 
                      Register.Title, MarkType.Mark, MarkType.Description, 
                      Enrolment.CompletionStatusID, Enrolment.StartDate, Enrolment.ExpectedEndDate, 
                      Enrolment.ActualEndDate, RegisterSession.Date AS Expr1
FROM         StudentDetail WITH (NOLOCK) INNER JOIN
                      Enrolment WITH (NOLOCK) ON Enrolment.StudentDetailID = StudentDetail.StudentDetailID INNER JOIN
                      Offering WITH (NOLOCK) ON Enrolment.OfferingID = Offering.OfferingID INNER JOIN
                      RegisterStudent WITH (NOLOCK) ON 
                      Enrolment.EnrolmentID = RegisterStudent.EnrolmentID INNER JOIN
                      Register WITH (NOLOCK) ON RegisterStudent.RegisterID = Register.RegisterID INNER JOIN
                      RegisterSession WITH (NOLOCK) ON Register.RegisterID = RegisterSession.RegisterID INNER JOIN
                      RegisterMark WITH (NOLOCK) ON 
                      RegisterSession.RegisterSessionID = RegisterMark.RegisterSessionID AND 
                      RegisterStudent.RegisterStudentID = RegisterMark.RegisterStudentID INNER JOIN
                      MarkType WITH (NOLOCK) ON RegisterMark.MarkTypeID = MarkType.MarkTypeID
GROUP BY StudentDetail.RefNo, StudentDetail.AcademicYearID, StudentDetail.FirstForename, 
                      StudentDetail.Surname, Offering.OfferingID, Offering.Code, Offering.Name, 
                      Register.RegisterID, Register.RegisterNo, Register.Title, 
                      Enrolment.CompletionStatusID, Enrolment.StartDate, Enrolment.ExpectedEndDate, 
                      Enrolment.ActualEndDate, MarkType.Mark, MarkType.Description, 
                      RegisterSession.Date
HAVING      (StudentDetail.AcademicYearID = '11/12') AND (Enrolment.CompletionStatusID = '1') AND 
                      (MarkType.Mark = 'W')
ORDER BY StudentDetail.RefNo, RegisterSession.Date

;WITH cteSource([Date], AcademicYearID, RefNo, FirstForename, Surname, OfferingID, Code, Name, RegisterID, RegisterNo, Title, Mark, [Description], CompletionStatusID, StartDate, ExpectedEndDate, ActualEndDate, Expr1, rn)
AS (
	SELECT		ses.[Date],
			sd.AcademicYearID,
			sd.RefNo,
			sd.FirstForename,
			sd.Surname,
			o.OfferingID,
			o.Code,
			o.Name,
			r.RegisterID,
			r.RegisterNo,
			r.Title,
			mt.Mark,
			mt.[Description],
			e.CompletionStatusID,
			e.StartDate,
			e.ExpectedEndDate,
			e.ActualEndDate,
			ses.[Date] AS Expr1,
			ROW_NUMBER() OVER (PARTITION BY sd.RefNo ORDER BY ses.[Date]) AS rn
	FROM		dbo.StudentDetail WITH (NOLOCK) AS sd
	INNER JOIN	dbo.Enrolment WITH (NOLOCK) AS e ON e.StudentDetailID = sd.StudentDetailID
				AND e.CompletionStatusID = '1'
	INNER JOIN	dbo.Offering WITH (NOLOCK) AS o ON o.OfferingID = e.OfferingID
	INNER JOIN	dbo.RegisterStudent WITH (NOLOCK) AS stu ON stu.EnrolmentID = e.EnrolmentID
	INNER JOIN	dbo.Register WITH (NOLOCK) AS r ON r.RegisterID = stu.RegisterID
	INNER JOIN	dbo.RegisterSession WITH (NOLOCK) AS ses ON ses.RegisterID = r.RegisterID
	INNER JOIN	dbo.RegisterMark WITH (NOLOCK) AS rm ON rm.RegisterSessionID = ses.RegisterSessionID
	INNER JOIN	dbo.MarkType WITH (NOLOCK) AS mt ON MarkType.MarkTypeID = rm.MarkTypeID
				AND mt.Mark = 'W'
	WHERE		sd.AcademicYearID = '11/12'
			AND rm.RegisterStudentID = rs.RegisterStudentID
)
SELECT		[Date],
		AcademicYearID,
		RefNo,
		FirstForename,
		Surname,
		OfferingID,
		Code,
		Name,
		RegisterID,
		RegisterNo,
		Title,
		Mark,
		[Description],
		CompletionStatusID,
		StartDate,
		ExpectedEndDate,
		ActualEndDate,
		Expr1
FROM		cteSource
WHERE		rn = 1
ORDER BY	RefNo;