Consolidated Result

ALTER PROCEDURE [dbo].[sp_ConsolidatedStudentResult]
(
@TermId varchar(50)
)
-- exec sp_ConsolidatedStudentResult 'CSE2012JAN_2K12Y1T1'
AS
BEGIN
DECLARE @StudentId int, @CourseId varchar(50)
declare ConsolidatedResult cursor for
SELECT DISTINCT dbo.T_Term_Wise_Course_Result.StudentId
FROM dbo.T_Term_Wise_Course_Result LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Wise_Course_Result.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Wise_Course_Result.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE T_Term_Wise_Course_Result.TermId=@TermId order by StudentId
open ConsolidatedResult
fetch next from ConsolidatedResult into
@StudentId
while @@FETCH_STATUS = 0
begin
Print 'Student Id: '+CAST(@StudentId as varchar(12))
DECLARE @DepartmentId varchar(50), @TotalEarnedCredit decimal(8,2), @TotalEarnedPoint decimal(8,2), @CGPA decimal(8,2)
SELECT @DepartmentId=DepartmentId FROM T_Term_Setup WHERE TermId=@TermId
Print 'Department: '+@DepartmentId
SELECT @TotalEarnedCredit = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Common_Student_Earned_Credit LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Common_Student_Earned_Credit.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Common_Student_Earned_Credit.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Common_Student_Earned_Credit.StudentId = @StudentId
SELECT @TotalEarnedPoint = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Common_Student_Earned_Credit.GradePoint * dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Common_Student_Earned_Credit LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Common_Student_Earned_Credit.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Common_Student_Earned_Credit.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Common_Student_Earned_Credit.StudentId = @StudentId
SELECT @CGPA = @TotalEarnedPoint/@TotalEarnedCredit
Print 'Total Earned Credit: '+ CAST(@TotalEarnedCredit as varchar(10))
Print 'CGPA: '+ CAST(@CGPA as varchar(10))
DECLARE @Count int
SELECT @Count = COUNT(*) FROM dbo.T_Student_Consolidated_Earned_Credit WHERE StudentId = @StudentId
if(@Count=0)
insert into dbo.T_Student_Consolidated_Earned_Credit(StudentId,DepartmentId,TotalEarnedCredit,CurrentCGPA) values(@StudentId,@DepartmentId,@TotalEarnedCredit,@CGPA)
else if(@Count>0)
update dbo.T_Student_Consolidated_Earned_Credit set TotalEarnedCredit = @TotalEarnedCredit, CurrentCGPA = @CGPA WHERE StudentId=@StudentId
print @Count
declare Course cursor for
SELECT DISTINCT dbo.T_Term_Wise_Course_Result.CourseId
FROM dbo.T_Term_Wise_Course_Result LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Wise_Course_Result.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Wise_Course_Result.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE T_Term_Wise_Course_Result.TermId=@TermId AND dbo.T_Term_Wise_Course_Result.StudentId= @StudentId
open Course
fetch next from Course into
@CourseId
while @@FETCH_STATUS = 0
begin
DECLARE @CourseDetails varchar(100), @CurrentTermId varchar(50), @Course varchar(50), @CourseCredit decimal(8,2), @LetterGrade varchar(10), @GradePoint decimal(8,2),
@TermType varchar(5), @CourseType varchar(20)
SELECT @CurrentTermId = dbo.T_Term_Wise_Course_Result.TermId, @Course = dbo.T_Term_Wise_Course_Result.CourseId, @CourseCredit = ISNULL(dbo.T_Term_Course_Offer.CourseCredit,0),
@LetterGrade = ISNULL(dbo.T_Term_Wise_Course_Result.LetterGrade,0), @GradePoint = ISNULL(dbo.T_Term_Wise_Course_Result.GradePoint,0),
@TermType = dbo.T_Term_Wise_Course_Result.TermType, @CourseType = dbo.T_Term_Course_Offer.CourseType
FROM dbo.T_Term_Wise_Course_Result LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Wise_Course_Result.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Wise_Course_Result.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Term_Wise_Course_Result.StudentId=@StudentId AND dbo.T_Term_Wise_Course_Result.TermId=@TermId
AND dbo.T_Term_Wise_Course_Result.CourseId=@CourseId order by dbo.T_Term_Wise_Course_Result.CourseId
SELECT @CourseDetails=@CurrentTermId+'('+@TermType+')'+'/'+@CourseId+'('+@CourseType+')'+'('+CAST(@CourseCredit AS varchar(5))+')'+'/'+@LetterGrade+'/'+CAST(@GradePoint AS varchar(5))
Print 'Details Result: '+@CourseDetails
fetch next from Course into
@CourseId
end
close Course
deallocate Course
fetch next from ConsolidatedResult into
@StudentId
end
close ConsolidatedResult
deallocate ConsolidatedResult
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