Stored Procedure: sp_sessional_marks_entry_select with NTILE technology

ALTER procedure [dbo].[sp_sessional_marks_entry_select]
(
@TermId varchar(30),
@CourseId varchar(30),
@NtileParam int
)
-- exec sp_sessional_marks_entry_select 'CE2012FEB_2K11Y1T1', 'CE2006JANCh1102',2
as
begin
SELECT StudentId,LabPerformance,Attendance,QuizViva,CourseTitle FROM(
SELECT StudentId,LabPerformance,Attendance,QuizViva,CourseTitle, NTILE(2) OVER(ORDER BY StudentId ASC) AS Row FROM
(
SELECT DISTINCT
dbo.T_Term_Student_Result_Lab_Details.StudentId,
Ceiling(IsNull(dbo.T_Term_Student_Result_Lab_Details.LabPerformance,0)) AS LabPerformance,
Ceiling(IsNull(dbo.T_Term_Student_Result_Lab_Details.Attendance,0)) AS Attendance,
Ceiling(IsNull(dbo.T_Term_Student_Result_Lab_Details.QuizViva,0)) AS QuizViva,
dbo.T_Term_Course_Offer.CourseTitle
FROM dbo.T_Term_Student_Result_Lab_Details
LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Student_Result_Lab_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Student_Result_Lab_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Term_Student_Result_Lab_Details.TermId=@TermId
AND dbo.T_Term_Student_Result_Lab_Details.CourseId=@CourseId
AND (T_Term_Student_Result_Lab_Details.StudentId
NOT IN (SELECT StudentId FROM T_StudentListBellow60PercentAttendance WHERE TermId=@TermId AND CourseId=@CourseId))
)T
)TT WHERE Row=@NtileParam
end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s