Static Column Result Table: Data Insert and Retrieve Procedure [Final] for Transcript Preparation- Working with Old Data

////////////////////////////////// Data Insert ///////////////////

USE [dbolddataconversion]
GO
/****** Object: StoredProcedure [dbo].[sp_ConsolidatedStudentCommonResult] Script Date: 11/20/2012 16:52:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Author: Md. Nazim Uddin>
— alter date: <alter Date: 20-11-2012>
— Description: <Description: old data conversion>

/*
Comments and direction to execute this procedure: this procedure need to run after the result
processing of each term. condition holds (a) after result processing of target term and (b)
run the recent/ current term, (c) random term execution will create wrong data all over the system
*/
— =============================================
ALTER PROCEDURE [dbo].[sp_ConsolidatedStudentCommonResult]
— exec sp_ConsolidatedStudentCommonResult
AS
BEGIN

DECLARE @TermId Varchar(100)

declare TermName cursor for
SELECT DISTINCT TermId FROM T_Term_Setup
open TermName
fetch next from TermName into
@TermId
while @@FETCH_STATUS = 0
begin

DECLARE @TestTermType varchar(2)
SELECT @TestTermType=TermType FROM T_Term_Setup WHERE TermId=@TermId

if(@TestTermType=’R’)
BEGIN
DECLARE @StudentId varchar(50), @CourseId varchar(50)

declare ConsolidatedStudentResult cursor for

SELECT DISTINCT StudentId
FROM T_Term_Std_Course_Reg
WHERE TermId=@TermId order by StudentId

open ConsolidatedStudentResult

fetch next from ConsolidatedStudentResult into
@StudentId
while @@FETCH_STATUS = 0

begin
DECLARE @EnrollmentSession varchar(50),@CurrentSession varchar(50), @DepartmentId varchar(50), @StartIndex int, @EndIndex int,@TermRegCredit decimal(8,2),
@TermEarnedCredit decimal(8,2),@TermEarnedCreditTheory decimal(8,2),@TermEarnedCreditSessional decimal(8,2),@TermEarnedCreditThesis decimal(8,2),
@TermEarnedGP decimal(8,2),@TermEarnedGPTheory decimal(8,2),@TermEarnedGPSessional decimal(8,2),@TermEarnedGPThesis decimal(8,2),
@TotalRegCredit decimal(8,2),@TotalEarnedCredit decimal(8,2),@TotalEarnedCreditTheory decimal(8,2),@TotalEarnedCreditSessional decimal(8,2),
@TotalEarnedCreditThesis decimal(8,2),@TotalEarnedPoint decimal(8,2),@TotalEarnedPointTheory decimal(8,2),@TotalEarnedPointSessional decimal(8,2),
@TotalEarnedPointThesis decimal(8,2),@TGPA decimal(8,2),@CGPA decimal(8,2)

SELECT @DepartmentId=DepartmentId FROM T_Term_Setup WHERE TermId=@TermId

DECLARE @Year INT, @Term INT
SELECT @Year= TargetYear, @Term=TargetTerm FROM T_Term_Setup WHERE TermId=@TermId
SELECT @EnrollmentSession = EnrollmentSession FROM dbo.T_Std_General_Information WHERE StudentId= @StudentId
SELECT @CurrentSession = AcademicSession FROM dbo.T_Term_Setup WHERE TermId=@TermId

———————————————– Start Term Reg Credit —————————————————————
SELECT @TermRegCredit= SUM(dbo.T_Term_Course_Offer.CourseCredit)
FROM dbo.T_Term_Std_Course_Reg LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Course_Reg.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Std_Course_Reg.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE (dbo.T_Term_Std_Course_Reg.StudentId = @StudentId) AND (dbo.T_Term_Std_Course_Reg.TermId = @TermId)

————————————————- End Term Reg Credit —————————————————————
———————————————– Start Term Earned Credit —————————————————————
SELECT @TermEarnedCreditTheory = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_Result_Theory_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_Result_Theory_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE (dbo.T_Term_Std_Result_Theory_Details.StudentId = @StudentId) AND (dbo.T_Term_Std_Result_Theory_Details.TermId = @TermId) AND T_Term_Std_Result_Theory_Details.LetterGrade NOT IN (‘F’,’X’,’I’)

SELECT @TermEarnedCreditSessional = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
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.StudentId = @StudentId AND dbo.T_Term_Student_Result_Lab_Details.TermId=@TermId AND T_Term_Student_Result_Lab_Details.LetterGrade NOT IN (‘F’,’X’,’I’)
IF(@Year=4 AND @Term=2)
BEGIN
SELECT @TermEarnedCreditThesis = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_ThesisProject_Result_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_ThesisProject_Result_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Term_Std_ThesisProject_Result_Details.StudentId = @StudentId AND dbo.T_Term_Std_ThesisProject_Result_Details.TermId=@TermId AND T_Term_Std_ThesisProject_Result_Details.LetterGrade NOT IN (‘F’,’X’,’I’)
END
ELSE
SET @TermEarnedCreditThesis=0

SET @TermEarnedCredit= isnull(@TermEarnedCreditTheory,0)+isnull(@TermEarnedCreditSessional,0)+isnull(@TermEarnedCreditThesis,0)
———————————————– End Term Earned Credit —————————————————————
———————————————– Start Term Earned Ponit (GP) ———————————————————–
SELECT @TermEarnedGPTheory = dbo.fn_TwoDigitAfterDecimalPoint(SUM(IsNull(dbo.T_Term_Std_Result_Theory_Details.GradePoint,0) * dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_Result_Theory_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_Result_Theory_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Term_Std_Result_Theory_Details.StudentId = @StudentId AND dbo.T_Term_Std_Result_Theory_Details.TermId=@TermId

SELECT @TermEarnedGPSessional = dbo.fn_TwoDigitAfterDecimalPoint(SUM(IsNull(dbo.T_Term_Student_Result_Lab_Details.GradePoint,0) * dbo.T_Term_Course_Offer.CourseCredit))
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.StudentId = @StudentId AND dbo.T_Term_Student_Result_Lab_Details.TermId=@TermId

IF(@Year=4 AND @Term=2)
BEGIN
SELECT @TermEarnedGPThesis = dbo.fn_TwoDigitAfterDecimalPoint(SUM(IsNull(dbo.T_Term_Std_ThesisProject_Result_Details.GradePoint,0) * dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_ThesisProject_Result_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_ThesisProject_Result_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
WHERE dbo.T_Term_Std_ThesisProject_Result_Details.StudentId = @StudentId AND dbo.T_Term_Std_ThesisProject_Result_Details.TermId=@TermId

END
ELSE
SET @TermEarnedGPThesis=0

SET @TermEarnedGP=isnull(@TermEarnedGPTheory,0)+isnull(@TermEarnedGPSessional,0)+isnull(@TermEarnedGPThesis,0)
———————————————– End Term Earned Ponit (GP) ———————————————————–
———————————————– Start Total Reg Credit —————————————————————

SELECT @TotalRegCredit= SUM(dbo.T_Term_Course_Offer.CourseCredit)
FROM dbo.T_Term_Std_Course_Reg LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Course_Reg.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Std_Course_Reg.CourseId = dbo.T_Term_Course_Offer.CourseId
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Std_Course_Reg.TermId = dbo.T_Term_Setup.TermId
WHERE (dbo.T_Term_Std_Course_Reg.StudentId = @StudentId)
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))
————————————————- End Total Reg Credit —————————————————————
———————————————– Start Total Earned Credit —————————————————————

SELECT @TotalEarnedCreditTheory = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_Result_Theory_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_Result_Theory_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Setup.TermId
WHERE dbo.T_Term_Std_Result_Theory_Details.StudentId = @StudentId AND T_Term_Std_Result_Theory_Details.LetterGrade NOT IN (‘F’,’X’,’I’)
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))

SELECT @TotalEarnedCreditSessional = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
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
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Student_Result_Lab_Details.TermId = dbo.T_Term_Setup.TermId
WHERE dbo.T_Term_Student_Result_Lab_Details.StudentId = @StudentId AND T_Term_Student_Result_Lab_Details.LetterGrade NOT IN (‘F’,’X’,’I’)
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))

IF(@Year=4 AND @Term=2)
BEGIN
SELECT @TotalEarnedCreditThesis = dbo.fn_TwoDigitAfterDecimalPoint(SUM(dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_ThesisProject_Result_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_ThesisProject_Result_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Setup.TermId
WHERE dbo.T_Term_Std_ThesisProject_Result_Details.StudentId = @StudentId AND T_Term_Std_ThesisProject_Result_Details.LetterGrade NOT IN (‘F’,’X’,’I’)
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))
END
ELSE
SET @TotalEarnedCreditThesis=0

SET @TotalEarnedCredit= isnull(@TotalEarnedCreditTheory,0)+isnull(@TotalEarnedCreditSessional,0)+isnull(@TotalEarnedCreditThesis,0)
———————————————– End Total Earned Credit —————————————————————
———————————————– Start Total Earned Ponit (GP) ———————————————————–

SELECT @TotalEarnedPointTheory = dbo.fn_TwoDigitAfterDecimalPoint(SUM(IsNull(dbo.T_Term_Std_Result_Theory_Details.GradePoint,0) * dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_Result_Theory_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_Result_Theory_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Setup.TermId
WHERE dbo.T_Term_Std_Result_Theory_Details.StudentId = @StudentId
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))

SELECT @TotalEarnedPointSessional = dbo.fn_TwoDigitAfterDecimalPoint(SUM(IsNull(dbo.T_Term_Student_Result_Lab_Details.GradePoint,0) * dbo.T_Term_Course_Offer.CourseCredit))
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
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Student_Result_Lab_Details.TermId = dbo.T_Term_Setup.TermId
WHERE dbo.T_Term_Student_Result_Lab_Details.StudentId = @StudentId
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))

IF(@Year=4 AND @Term=2)
BEGIN
SELECT @TotalEarnedPointThesis = dbo.fn_TwoDigitAfterDecimalPoint(SUM(IsNull(dbo.T_Term_Std_ThesisProject_Result_Details.GradePoint,0) * dbo.T_Term_Course_Offer.CourseCredit))
FROM dbo.T_Term_Std_ThesisProject_Result_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Course_Offer.TermId
AND dbo.T_Term_Std_ThesisProject_Result_Details.CourseId = dbo.T_Term_Course_Offer.CourseId
LEFT OUTER JOIN T_Term_Setup ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Setup.TermId
WHERE dbo.T_Term_Std_ThesisProject_Result_Details.StudentId = @StudentId
AND ((T_Term_Setup.TargetYear<@Year) or (T_Term_Setup.TargetYear=@Year AND T_Term_Setup.TargetTerm<=@Term))
END
ELSE
SET @TotalEarnedPointThesis=0

SET @TotalEarnedPoint=isnull(@TotalEarnedPointTheory,0)+isnull(@TotalEarnedPointSessional,0)+isnull(@TotalEarnedPointThesis,0)
———————————————– End Total Earned Ponit (GP) ———————————————————–
————————————— Start Total Earned Credit & CGPA: Insert to T_Student_Consolidated_Earned_Credit ————————————-

SET @CGPA = @TotalEarnedPoint/@TotalEarnedCredit
SET @TGPA = @TermEarnedGP/@TermEarnedCredit

DECLARE @Count int
SELECT @Count = COUNT(*) FROM dbo.T_Common_Student_Consolidated_Earned_Credit WHERE StudentId = @StudentId
if(@Count=0)
insert into dbo.T_Common_Student_Consolidated_Earned_Credit(StudentId,DepartmentId,EnrollmentSession,CurrentSession, TotalEarnedCredit,CurrentCGPA, LoopingValue) values(@StudentId,@DepartmentId , @EnrollmentSession,@CurrentSession ,@TotalEarnedCredit,@CGPA,8)
else if(@Count>0)
update dbo.T_Common_Student_Consolidated_Earned_Credit set TotalEarnedCredit = @TotalEarnedCredit, CurrentCGPA = @CGPA, EnrollmentSession=@EnrollmentSession,CurrentSession=@CurrentSession WHERE StudentId=@StudentId
————————————— End Total Earned Credit & CGPA: Insert to T_Student_Consolidated_Earned_Credit ————————————-
———————————————— Start: T_Common_Student_Earned_Credit_Term_Index table entry ————————————————–
DECLARE @T1 varchar(100),@T2 varchar(100),@T3 varchar(100),@T4 varchar(100),@T5 varchar(100),
@T6 varchar(100),@T7 varchar(100),@T8 varchar(100),@T9 varchar(100),@T10 varchar(100),
@T11 varchar(100),@T12 varchar(100),@T13 varchar(100),@T14 varchar(100),@T15 varchar(100),
@T16 varchar(100),@T17 varchar(100),@T18 varchar(100),@T19 varchar(100),@T20 varchar(100), @TermIdEntry varchar(100)

SELECT @T1=T1, @T2=T2, @T3=T3, @T4=T4, @T5=T5,@T6=T6, @T7=T7, @T8=T8, @T9=T9, @T10=T10,@T11=T11, @T12=T12, @T13=T13, @T14=T14, @T15=T15,@T16=T16, @T17=T17, @T18=T18, @T19=T19, @T20=T20 FROM T_Common_Student_Earned_Credit_Term_Index WHERE StudentId=@StudentId

IF(@T1 is null) BEGIN SET @TermIdEntry=’T1′ GOTO ENDLABEL; END
ELSE IF(@T2 is null) BEGIN SET @TermIdEntry=’T2′ GOTO ENDLABEL; END
ELSE IF(@T3 is null) BEGIN SET @TermIdEntry=’T3′ GOTO ENDLABEL; END
ELSE IF(@T4 is null) BEGIN SET @TermIdEntry=’T4′ GOTO ENDLABEL; END
ELSE IF(@T5 is null) BEGIN SET @TermIdEntry=’T5′ GOTO ENDLABEL; END
ELSE IF(@T6 is null) BEGIN SET @TermIdEntry=’T6′ GOTO ENDLABEL; END
ELSE IF(@T7 is null) BEGIN SET @TermIdEntry=’T7′ GOTO ENDLABEL; END
ELSE IF(@T8 is null) BEGIN SET @TermIdEntry=’T8′ GOTO ENDLABEL; END
ELSE IF(@T9 is null) BEGIN SET @TermIdEntry=’T9′ GOTO ENDLABEL; END
ELSE IF(@T10 is null) BEGIN SET @TermIdEntry=’T10′ GOTO ENDLABEL; END
ELSE IF(@T11 is null) BEGIN SET @TermIdEntry=’T11′ GOTO ENDLABEL; END
ELSE IF(@T12 is null) BEGIN SET @TermIdEntry=’T12′ GOTO ENDLABEL; END
ELSE IF(@T13 is null) BEGIN SET @TermIdEntry=’T13′ GOTO ENDLABEL; END
ELSE IF(@T14 is null) BEGIN SET @TermIdEntry=’T14′ GOTO ENDLABEL; END
ELSE IF(@T15 is null) BEGIN SET @TermIdEntry=’T15′ GOTO ENDLABEL; END
ELSE IF(@T16 is null) BEGIN SET @TermIdEntry=’T16′ GOTO ENDLABEL; END
ELSE IF(@T17 is null) BEGIN SET @TermIdEntry=’T17′ GOTO ENDLABEL; END
ELSE IF(@T18 is null) BEGIN SET @TermIdEntry=’T18′ GOTO ENDLABEL; END
ELSE IF(@T19 is null) BEGIN SET @TermIdEntry=’T19′ GOTO ENDLABEL; END
ELSE IF(@T20 is null) BEGIN SET @TermIdEntry=’T20′ GOTO ENDLABEL; END

ENDLABEL:
———————————————— End: T_Common_Student_Earned_Credit_Term_Index table entry ————————————————–
——————————————————- Start Declaration and initialization Section ——————————————————————————-
DECLARE @Y INT, @T INT

DECLARE @UpdateCol INT,@NoofCourse int,@PreviousLoopingValue varchar(100),@Index1 int,@ProcessedTerm varchar(100),@Countvalue1 int,@SametermRepeated int, @InsertDecision INT
set @NoofCourse = 0
set @Index1 = 0
set @Countvalue1 = 0
set @ProcessedTerm = ”
set @SametermRepeated = 0
SET @InsertDecision=0

SET @UpdateCol = (select LoopingValue from T_Common_Student_Consolidated_Earned_Credit where StudentId = @StudentId)
SET @NoofCourse = (select LoopingValue from T_Common_Student_Consolidated_Earned_Credit where StudentId = @StudentId)

SET @StartIndex = @UpdateCol
——————————————————- End Declaration and initialization Section ——————————————————————————-
——————– Start: This is for processing multiple time for a specific term ————————–
select @PreviousLoopingValue = PreviousLoopingValue from T_Common_Student_Consolidated_Earned_Credit where StudentId = @StudentId

if(@PreviousLoopingValue <> ” and @PreviousLoopingValue is not null)
begin
select @Index1 = PATINDEX(‘%/%’,@PreviousLoopingValue)
select @ProcessedTerm = RTRIM(ltrim( SUBSTRING(@PreviousLoopingValue,1,@Index1 – 1)))
select @Countvalue1 = CAST( RTRIM(ltrim( SUBSTRING(@PreviousLoopingValue,@Index1 + 1,LEN(@PreviousLoopingValue)- @Index1))) as int)

if(@ProcessedTerm = @TermId)
begin
set @UpdateCol = @Countvalue1
set @NoofCourse = @Countvalue1
set @SametermRepeated = 1
SET @InsertDecision=1
end
end

update T_Common_Student_Consolidated_Earned_Credit
set PreviousLoopingValue = @TermId + ‘/’+ CAST(@NoofCourse as varchar(5))
where StudentId = @StudentId
——————– End: This is for processing multiple time for a specific term ————————–

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)

declare CourseDetails cursor for
SELECT CourseId FROM T_Term_Std_Course_Reg WHERE TermId=@TermId AND StudentId=@StudentId ORDER BY CourseId
————————————————————– Start: Course Looping : Course Courser ————————————-
open CourseDetails

fetch next from CourseDetails into
@CourseId
while @@FETCH_STATUS = 0
begin
set @NoofCourse = @NoofCourse + 1
SELECT @CourseType = CourseType FROM T_Term_Course_Offer WHERE TermId=@TermId AND CourseId=@CourseId

IF(@CourseType=’Theory’)
BEGIN
SELECT @CurrentTermId = dbo.T_Term_Std_Result_Theory_Details.TermId,
@Course = dbo.T_Term_Std_Result_Theory_Details.CourseId,
@CourseCredit = ISNULL(dbo.T_Term_Course_Offer.CourseCredit,0),
@LetterGrade = dbo.T_Term_Std_Result_Theory_Details.LetterGrade,
@GradePoint = ISNULL(dbo.T_Term_Std_Result_Theory_Details.GradePoint,0)

FROM dbo.T_Term_Std_Result_Theory_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_Result_Theory_Details.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Std_Result_Theory_Details.CourseId = dbo.T_Term_Course_Offer.CourseId

WHERE dbo.T_Term_Std_Result_Theory_Details.StudentId=@StudentId AND dbo.T_Term_Std_Result_Theory_Details.TermId=@TermId
AND dbo.T_Term_Std_Result_Theory_Details.CourseId=@CourseId
ORDER BY dbo.T_Term_Std_Result_Theory_Details.CourseId

SELECT @TermType = TermType FROM T_Term_Setup WHERE TermId=@CurrentTermId
END

ELSE IF(@CourseType=’Thesis’)
BEGIN
if(@Y=4 AND @T=2)
BEGIN
SELECT @CurrentTermId = dbo.T_Term_Std_ThesisProject_Result_Details.TermId,
@Course = dbo.T_Term_Std_ThesisProject_Result_Details.CourseId,
@CourseCredit = ISNULL(dbo.T_Term_Course_Offer.CourseCredit,0),
@LetterGrade = ISNULL(dbo.T_Term_Std_ThesisProject_Result_Details.LetterGrade,0),
@GradePoint = ISNULL(dbo.T_Term_Std_ThesisProject_Result_Details.GradePoint,0)

FROM dbo.T_Term_Std_ThesisProject_Result_Details LEFT OUTER JOIN
dbo.T_Term_Course_Offer ON dbo.T_Term_Std_ThesisProject_Result_Details.TermId = dbo.T_Term_Course_Offer.TermId AND
dbo.T_Term_Std_ThesisProject_Result_Details.CourseId = dbo.T_Term_Course_Offer.CourseId

WHERE dbo.T_Term_Std_ThesisProject_Result_Details.StudentId=@StudentId AND dbo.T_Term_Std_ThesisProject_Result_Details.TermId=@TermId
AND dbo.T_Term_Std_ThesisProject_Result_Details.CourseId=@CourseId
ORDER BY dbo.T_Term_Std_ThesisProject_Result_Details.CourseId

SELECT @TermType = TermType FROM T_Term_Setup WHERE TermId=@CurrentTermId
END
END

ELSE
BEGIN
SELECT @CurrentTermId = dbo.T_Term_Student_Result_Lab_Details.TermId,
@Course = dbo.T_Term_Student_Result_Lab_Details.CourseId,
@CourseCredit = ISNULL(dbo.T_Term_Course_Offer.CourseCredit,0),
@LetterGrade = ISNULL(dbo.T_Term_Student_Result_Lab_Details.LetterGrade,0),
@GradePoint = ISNULL(dbo.T_Term_Student_Result_Lab_Details.GradePoint,0)

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.StudentId=@StudentId AND dbo.T_Term_Student_Result_Lab_Details.TermId=@TermId
AND dbo.T_Term_Student_Result_Lab_Details.CourseId=@CourseId

ORDER BY dbo.T_Term_Student_Result_Lab_Details.CourseId
SELECT @TermType = TermType FROM T_Term_Setup WHERE TermId=@CurrentTermId

END

SET @CourseDetails=”
SET @CourseDetails=IsNull(@TermIdEntry,”)+'(‘+IsNull(@TermType,”)+’)’+’/’+IsNull(@CourseId,”)+'(‘+CAST(IsNull(@CourseCredit,0) AS varchar(8))+’)’+’/’+IsNull(@LetterGrade,0)+’/’+CAST(IsNull(@GradePoint,0) AS varchar(8))

DECLARE @ColumnNext INT
SET @ColumnNext=@UpdateCol
DECLARE @Loop INT

————————————————– Start: Loop 1-111, read the column name dynamically ——————————-
SET @Loop = 1
WHILE(@Loop <= 111)
BEGIN
Declare @WhichOne int, @QuoiteVariable varchar(4)
,@Sql nvarchar(max),@ColumValueOutput varchar(max),@pamarmDefination nvarchar(MAX)
SET @QuoiteVariable =””;

if(@SametermRepeated <> 1)
begin
SET @WhichOne = @ColumnNext;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = ‘T_Common_Student_Consolidated_Earned_Credit’)
SELECT @Sql = ‘IF((SELECT ‘ + name + ‘ FROM T_Common_Student_Consolidated_Earned_Credit WHERE StudentId = ‘ + CAST( @StudentId as varchar(20)) + ‘) is null ) BEGIN UPDATE T_Common_Student_Consolidated_Earned_Credit SET ‘ + ‘ ‘ + name + ‘ = ‘ + @QuoiteVariable + @CourseDetails + @QuoiteVariable + ‘ WHERE StudentId = ‘ + CAST( @StudentId as varchar(20))+’ END’
FROM cte
WHERE rn = @WhichOne;
Exec(@Sql);

set @Sql = ”
SET @WhichOne = @ColumnNext;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = ‘T_Common_Student_Consolidated_Earned_Credit’)
SELECT @Sql = ‘SELECT @ColumValue = ‘ + name + ‘ FROM T_Common_Student_Consolidated_Earned_Credit WHERE StudentId = ‘ + CAST( @StudentId as varchar(20)) + ”
FROM cte
WHERE rn = @WhichOne;

set @pamarmDefination=’@ColumValue varchar(max) output’
exec sp_executeSQL @Sql,@pamarmDefination,@ColumValue = @ColumValueOutput output

if(@ColumValueOutput is not null)
GOTO EXITLABEL
end
else if(@SametermRepeated = 1)
begin
DECLARE @TableColumnStr varchar(10), @TableColumn varchar(10), @CourseDetailsUpdate varchar(200)
SET @TableColumnStr=SUBSTRING(@TermIdEntry,2,LEN(@TermIdEntry))
SET @TableColumn=’T’+CAST((CAST(@TableColumnStr AS INT)-1) AS varchar(10))
SET @CourseDetailsUpdate=IsNull(@TableColumn,”)+'(‘+IsNull(@TermType,”)+’)’+’/’+IsNull(@CourseId,”)+'(‘+CAST(IsNull(@CourseCredit,0) AS varchar(8))+’)’+’/’+IsNull(@LetterGrade,0)+’/’+CAST(IsNull(@GradePoint,0) AS varchar(8))

SET @WhichOne = @ColumnNext;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = ‘T_Common_Student_Consolidated_Earned_Credit’)
SELECT @Sql = ‘ UPDATE T_Common_Student_Consolidated_Earned_Credit SET ‘ + ‘ ‘ + name + ‘ = ‘ + @QuoiteVariable + @CourseDetailsUpdate + @QuoiteVariable + ‘ WHERE StudentId = ‘ + CAST( @StudentId as varchar(20))+’ ‘
FROM cte
WHERE rn = @WhichOne;
Exec(@Sql);

set @Sql = ”
SET @WhichOne = @ColumnNext;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = ‘T_Common_Student_Consolidated_Earned_Credit’)
SELECT @Sql = ‘SELECT @ColumValue = ‘ + name + ‘ FROM T_Common_Student_Consolidated_Earned_Credit WHERE StudentId = ‘ + CAST( @StudentId as varchar(20)) + ”
FROM cte
WHERE rn = @WhichOne;

set @pamarmDefination=’@ColumValue varchar(max) output’
exec sp_executeSQL @Sql,@pamarmDefination,@ColumValue = @ColumValueOutput output

if(@ColumValueOutput is not null)
GOTO EXITLABEL
end

SET @Loop=@Loop+1
SET @ColumnNext=@ColumnNext+1
END
————————————————– End: Loop 1-111, read the column name dynamically ——————————-
EXITLABEL:
SET @UpdateCol = @UpdateCol + 1

————This is for set Looping Variable value———————-
update T_Common_Student_Consolidated_Earned_Credit
set LoopingValue = @NoofCourse
where StudentId = @StudentId

fetch next from CourseDetails into
@CourseId
end
close CourseDetails
deallocate CourseDetails
————————————————————– End: Course Looping : Course Courser ————————————-
———————————————————– Start Index Table Entry ————————————————-

SELECT @EndIndex = LoopingValue FROM T_Common_Student_Consolidated_Earned_Credit WHERE StudentId = @StudentId

DECLARE @CountIndex int, @IndexString varchar(200)
SELECT @CountIndex = COUNT(*) FROM dbo.T_Common_Student_Earned_Credit_Term_Index WHERE StudentId = @StudentId
if(@CountIndex=0)
insert into dbo.T_Common_Student_Earned_Credit_Term_Index(StudentId) values(@StudentId)
SET @IndexString = ”” + @TermId +'(‘+@TermIdEntry+’)’+ ‘#’ + CAST(@StartIndex as varchar(100)) + ‘-‘ + CAST((@EndIndex-1) as varchar(100))+ ‘#’ + CAST(@TermRegCredit as varchar(100)) + ‘/’ + CAST(@TermEarnedCredit as varchar(100)) + ‘#’ + CAST(@TotalRegCredit as varchar(100)) +’/’+ CAST(@TotalEarnedCredit as varchar(100)) +’#’+ CAST(@TermEarnedGP as varchar(100))+ ‘#’ + CAST(@TotalEarnedPoint as varchar(100))+’#’+””

IF(@InsertDecision=0)
BEGIN
declare @sqlcomm nvarchar(2000)
set @sqlcomm = ‘UPDATE T_Common_Student_Earned_Credit_Term_Index SET ‘ + @TermIdEntry + ‘=’ + @IndexString + ‘ WHERE StudentId= ‘ + CAST(@StudentId as varchar(100))
EXEC(@sqlcomm)
END
ELSE IF(@InsertDecision=1)
BEGIN
declare @sqlcommand nvarchar(2000)
END
———————————————————– End Index Table Entry ————————————————-
fetch next from ConsolidatedStudentResult into
@StudentId
end
close ConsolidatedStudentResult
deallocate ConsolidatedStudentResult
END

fetch next from TermName into
@TermId
end
close TermName
deallocate TermName
END

 

///////////////// Data Retrieve ////////////////////////////////////

USE [dbolddataconversion]
GO
/****** Object: StoredProcedure [dbo].[sp_TranscriptReportDataGeneration] Script Date: 11/20/2012 17:17:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— exec sp_ConsolidatedData
ALTER PROC [dbo].[sp_TranscriptReportDataGeneration]
(
@StudentId varchar(50)
)
— exec sp_TranscriptReportDataGeneration 0501002
AS
BEGIN

DECLARE @String VARCHAR(100),
@TermId VARCHAR(50),
@CourseId VARCHAR(50),
@LetterGrade VARCHAR(5),
@GP VARCHAR(6),
@STRTPOS INT,
@CREDIT VARCHAR(10),
@NAME VARCHAR(100),
@DEPT VARCHAR(100),
@ExamName varchar(100),
@TargetYear int,
@TargetTerm int,
@AcademicYear int,
@CourseName varchar(50)

SELECT @NAME = StudentsName FROM T_Std_General_Information WHERE StudentId=@StudentId
SELECT @DEPT = departmentName FROM T_Common_Department_Setup WHERE departmentId = (SELECT DepartmentId FROM T_Std_General_Information WHERE StudentId=@StudentId)

declare @TempTab1 table
(
StudentId varchar(50),
StudentName varchar(100),
Department varchar(50),
TermIndex varchar(50),
CourseId varchar(50),
Credit decimal(8,2),
Grade varchar(10),
GradePoint decimal(8,2)
)
declare @Tabstring nvarchar(max)
declare @Tabname nvarchar(100)
set @Tabname=’tblTempTab1′
set @Tabstring=’create table #’ + @Tabname
Declare @WhichOne int,
@UpdateCol INT,
@Loop INT

SET @Loop=111;
SET @UpdateCol=8;
WHILE(@Loop != 0)
BEGIN

Declare @Sql nvarchar(max),
@ParameterDefination nvarchar(max)
SET @WhichOne = @UpdateCol;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = ‘T_Common_Student_Consolidated_Earned_Credit’)
SELECT @CourseName = CAST(name AS varchar(50)) FROM cte WHERE rn = @WhichOne;

set @Sql = ‘select @String1 = ‘ + @CourseName + ‘ from T_Common_Student_Consolidated_Earned_Credit where StudentId = ‘ + CAST(@StudentId as varchar(20))
set @ParameterDefination = ‘@String1 varchar(200) output’
exec sp_EXECUTESQL @Sql, @ParameterDefination ,@String1 = @String output

if(Isnull(@String,”) != ”)
BEGIN

DECLARE
@STR VARCHAR(100),
@SUBSTR1 VARCHAR(100),
@SUBSTR2 VARCHAR(100),
@SUBSTR3 VARCHAR(100),
@SUBSTR4 VARCHAR(100)

SET @STR=@String –‘T1(R)/CSE2006MAYCSE1101(3.00)/A+/4.00’

SELECT @TermId = LEFT(@STR,PATINDEX(‘%(%)/%’,@STR)-1)
SELECT @SUBSTR1 = RIGHT(@STR,LEN(@STR)-CHARINDEX(‘/’,@STR))

SELECT @CourseId = LEFT(@SUBSTR1,PATINDEX(‘%(%)/%’,@SUBSTR1)-1)
SELECT @SUBSTR2 = RIGHT(@SUBSTR1,LEN(@SUBSTR1)-CHARINDEX(‘/’,@SUBSTR1))

SELECT @CREDIT = SUBSTRING(@SUBSTR1,PATINDEX(‘%(%’,@SUBSTR1)+1,4)

SELECT @LetterGrade = LEFT(@SUBSTR2,PATINDEX(‘%/%’,@SUBSTR2)-1)
SELECT @SUBSTR3 = RIGHT(@SUBSTR2,LEN(@SUBSTR2)-CHARINDEX(‘/’,@SUBSTR2))

SELECT @SUBSTR4 = RIGHT(@SUBSTR3,LEN(@SUBSTR3)-CHARINDEX(‘/’,@SUBSTR3))
SELECT @GP=@SUBSTR4

SELECT @TargetYear=TargetYear,@AcademicYear=AcademicYear FROM T_Term_Setup WHERE TermId=@TermId

SELECT @TargetTerm=TargetTerm FROM T_Term_Course_Offer WHERE TermId=@TermId AND CourseId=@CourseId

select @ExamName= dbo.IntToStr(CONVERT(varchar,@TargetYear))+’ Year ‘+dbo.IntToStr(CONVERT(varchar,@TargetTerm))+’ Term Examination ‘+ CAST(@AcademicYear AS varchar(10))
PRINT @ExamName

insert into @TempTab1
(
StudentId,
StudentName,
Department,
TermIndex,
CourseId,
Credit,
Grade,
GradePoint
)
VALUES(@StudentId,@NAME,@DEPT,@TermId,@CourseId,@CREDIT,@LetterGrade,@GP)

END

SELECT @UpdateCol=@UpdateCol+1
SELECT @Loop=@Loop-1
END

–SELECT * FROM @TempTab1

———————————————- Index table —————————————-
declare @TempTab2 table
(
TermIndex varchar(100),
TermIdMain varchar(100),
TermTakenCredit decimal(8,2),
TermCompletedCredit decimal(8,2),
TotalTakenCredit decimal(8,2),
TotalCompletedCredit decimal(8,2),
TermEarnedPoint decimal(8,2),
TotalEarnedPoint decimal(8,2)
)
declare @Tab2string nvarchar(max)
declare @Tab2name nvarchar(100)
set @Tab2name=’tblTempTab1′
set @Tab2string=’create table #’ + @Tab2name

Declare @WhichItem int,
@UpdateColumn INT,
@Looping INT,
@QString1 varchar(1000),
@QString varchar(1000),
@TermIndex varchar(500)

SET @Looping=20;
SET @UpdateColumn=2;

DECLARE @TermIdIndex varchar(10),
@TermIdMain varchar(100),
@TermTakenCompletedCredit varchar(100),
@TermTakenCredit varchar(100),
@TermCompletedCredit varchar(100),
@TotalTakenCompletedCredit varchar(100),
@TotalTakenCredit varchar(100),
@TotalCompletedCredit varchar(100),
@TermEarnedPoint varchar(50),
@TotalEarnedPoint varchar(50),
@RawString varchar(50)

WHILE(@Looping != 0)
BEGIN

Declare @SqlStr nvarchar(max),
@ParameterDefn nvarchar(max)
SET @WhichItem = @UpdateColumn;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = ‘T_Common_Student_Earned_Credit_Term_Index’)
SELECT @TermIndex = CAST(name AS varchar(50)) FROM cte WHERE rn = @WhichItem;

set @SqlStr = ‘select @QString1 = ‘ + @TermIndex + ‘ from T_Common_Student_Earned_Credit_Term_Index where StudentId = ‘ + CAST(@StudentId as varchar(20))
set @ParameterDefn = ‘@QString1 varchar(200) output’
exec sp_EXECUTESQL @SqlStr, @ParameterDefn ,@QString1 = @QString output

if(@QString is not null)
begin
–SELECT @QString — CSE2012JUL_2K11Y1T1(T1)#8-11#9.00/9.00#9.00/9.00#33.75#33.75#
SELECT @TermIdIndex = SUBSTRING(@QString,PATINDEX(‘%(%’,@QString)+1,((PATINDEX(‘%)%’,@QString)-1)-(PATINDEX(‘%(%’,@QString))))
–SELECT @TermIdIndex
SELECT @TermIdMain = LEFT(@QString,PATINDEX(‘%(%’,@QString)-1)
–SELECT @TermIdMain
–PRINT @QString
SELECT @RawString=RIGHT(@QString,(LEN(@QString)-PATINDEX(‘%#%’,@QString)))
–PRINT @RawString
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX(‘%#%’,@RawString)))
–PRINT @RawString
SELECT @TermTakenCompletedCredit=LEFT(@RawString,PATINDEX(‘%#%’,@RawString)-1)
–PRINT @TermTakenCompletedCredit
SELECT @TermTakenCredit=LEFT(@TermTakenCompletedCredit,PATINDEX(‘%/%’,@TermTakenCompletedCredit)-1)
SELECT @TermCompletedCredit=RIGHT(@TermTakenCompletedCredit,PATINDEX(‘%/%’,@TermTakenCompletedCredit)-1)
–PRINT @TermTakenCredit
–PRINT @TermCompletedCredit
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX(‘%#%’,@RawString)))
–PRINT @RawString
SELECT @TotalTakenCompletedCredit=LEFT(@RawString,PATINDEX(‘%#%’,@RawString)-1)
–PRINT @TotalTakenCompletedCredit
SELECT @TotalTakenCredit=LEFT(@TotalTakenCompletedCredit,PATINDEX(‘%/%’,@TotalTakenCompletedCredit)-1)
SELECT @TotalCompletedCredit=RIGHT(@TotalTakenCompletedCredit,PATINDEX(‘%/%’,@TotalTakenCompletedCredit)-1)
–PRINT @TotalTakenCredit
–PRINT @TotalCompletedCredit
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX(‘%#%’,@RawString)))
–PRINT @RawString
SELECT @TermEarnedPoint=LEFT(@RawString,PATINDEX(‘%#%’,@RawString)-1)
–PRINT @TermEarnedPoint
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX(‘%#%’,@RawString)))
–PRINT @RawString
SELECT @TotalEarnedPoint=LEFT(@RawString,PATINDEX(‘%#%’,@RawString)-1)
–PRINT @TotalEarnedPoint

insert into @TempTab2
(
TermIndex,
TermIdMain,
TermTakenCredit,
TermCompletedCredit,
TotalTakenCredit,
TotalCompletedCredit,
TermEarnedPoint,
TotalEarnedPoint
)
VALUES(@TermIdIndex,@TermIdMain,@TermTakenCredit,@TermCompletedCredit,@TotalTakenCredit,@TotalCompletedCredit,@TermEarnedPoint,@TotalEarnedPoint)

end

SELECT @UpdateColumn=@UpdateColumn+1
SELECT @Looping=@Looping-1
END

SELECT DISTINCT TT1.StudentId,
TT1.StudentName,
TT1.Department,
TT1.TermIndex,
TT1.CourseId,
TT1.Credit,
TT1.Grade,
TT1.GradePoint,TT2.TermIdMain,
TT2.TermTakenCredit,
TT2.TermCompletedCredit,
TT2.TotalTakenCredit,
TT2.TotalCompletedCredit,
TT2.TermEarnedPoint,
TT2.TotalEarnedPoint,
TS.TermName,
TS.TargetYear,
TS.TargetTerm,
TS.AcademicSession,
TS.AcademicYear,
(DATENAME(month,CAST(TS.TermOpenDate as DATE))+’ ‘+DATENAME(year,CAST(TS.TermOpenDate as DATE)))as FROMDATE,
(DATENAME(month,CAST(TS.ResultPublication as DATE))+’ ‘+DATENAME(year,CAST(TS.ResultPublication as DATE))) as TODATE ,
TCO.CourseCode,
TCO.CourseTitle
FROM @TempTab1 TT1 inner join
@TempTab2 TT2 on TT1.TermIndex = TT2.TermIndex inner join
T_Term_Setup TS on TS.TermId = TT2.TermIdMain inner join T_Term_Course_Offer TCO on TCO.TermId=TT2.TermIdMain and TCO.CourseId=TT1.CourseId

WHERE StudentId=@StudentId
ORDER BY TS.AcademicYear, TS.TargetYear,TS.TargetTerm, TCO.CourseCode

END

— exec sp_TranscriptReportDataGeneration 1107003

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