GO
/****** Object: StoredProcedure [dbo].[sp_TabulationSheetDeptTermWise] Script Date: 11/21/2011 09:56:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_TabulationSheetDeptTermWise]
(
@TermId varchar(50),
@QuoteVariable varchar(1)
)
as
–exec sp_TabulationSheetDeptTermWise ‘CSE2011NOV_2K11Y1T1’
begin
DECLARE @Cols NVARCHAR(2000),
@Parameter NVARCHAR(2000),
@StudentId int,
@StudentName varchar(100),
@DeptId varchar(50),
@CourseCode1 varchar(50),
@CourseCode2 varchar(50),
@CourseCode3 varchar(50),
@CourseCode4 varchar(50),
@CourseCode5 varchar(50),
@CourseCode6 varchar(50),
@CourseCode7 varchar(50),
@CourseCode8 varchar(50),
@CourseCode9 varchar(50),
@CourseCode10 varchar(50),
@CourseCode11 varchar(50),
@CourseCode12 varchar(50),
@LetterGrade varchar(5),
@IDX int,
@CountId int,
@Cols1 NVARCHAR(2000),
@HeaderShow NVARCHAR(2000)
set @IDX= 1
set @CountId = 0
SET @Cols = ”
set @HeaderShow = ”
set @Parameter = ‘@Cols NVARCHAR(2000)’
–collect distinct Course Code that we have in tblStudentResult
SELECT @Cols = @Cols + ‘[‘ + CourseCode + ‘]’ + ‘, ‘ FROM
(SELECT DISTINCT isnull(CourseCode,’N/A’) as CourseCode FROM
vw_TabulationSheetDeptTermWise WHERE TermId = @TermId) AS s order by s.CourseCode
SET @Cols = LEFT(@Cols,LEN(@Cols)-1)
SELECT @HeaderShow = @HeaderShow + CourseCode + ‘, ‘ FROM
(SELECT DISTINCT isnull(CourseCode,’N/A’) as CourseCode FROM
vw_TabulationSheetDeptTermWise WHERE TermId = @TermId) AS s order by s.CourseCode
SET @HeaderShow = LEFT(@HeaderShow,LEN(@HeaderShow)-1)
set @Cols1 = @Cols
WHILE @IDX!= 0
BEGIN
SET @IDX = CHARINDEX(‘,’,@Cols1)
IF @IDX != 0
SET @CountId = @CountId + 1
ELSE
SET @CountId = @CountId + 1
SET @Cols1 = RIGHT(@Cols1,LEN(@Cols1)-@IDX)
IF LEN(@Cols1)= 0
BREAK
END
–select @CountId
if(@CountId = 1)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’ + ‘,’+ ‘,’+ ‘,’+ ‘,’+ ‘,’+ ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’ + ‘,[……]’+ ‘,[…….]’+ ‘,[……..]’+ ‘,[………]’+ ‘,[……….]’+ ‘,[………..]’
end
else if(@CountId = 2)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’ + ‘,’+ ‘,’+ ‘,’+ ‘,’+ ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’ + ‘,[……]’+ ‘,[…….]’+ ‘,[……..]’+ ‘,[………]’+ ‘,[……….]’
end
else if(@CountId = 3)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’ + ‘,’+ ‘,’+ ‘,’+ ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’ + ‘,[……]’+ ‘,[…….]’+ ‘,[……..]’+ ‘,[………]’
end
else if(@CountId = 4)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’ + ‘,’+ ‘,’+ ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’ + ‘,[……]’+ ‘,[…….]’+ ‘,[……..]’
end
else if(@CountId = 5)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’ + ‘,’+ ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’ + ‘,[……]’+ ‘,[…….]’
end
else if(@CountId = 6)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’ + ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’ + ‘,[……]’
end
else if(@CountId = 7)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’+ ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’+ ‘,[…..]’
end
else if(@CountId = 8)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’ + ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’ + ‘,[….]’
end
else if(@CountId = 9)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’ + ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’ + ‘,[…]’
end
else if(@CountId = 10)
begin
set @HeaderShow = @HeaderShow + ‘,’ + ‘,’
set @Cols = @Cols + ‘,[.]’ + ‘,[..]’
end
else if(@CountId = 11)
begin
set @HeaderShow = @HeaderShow + ‘,’
set @Cols = @Cols + ‘,[.]’
end
else if(@CountId = 12)
begin
set @HeaderShow = @HeaderShow
set @Cols = @Cols
end
select @HeaderShow as MainRow
——–select @Cols
delete from T_TempReport
declare @sqlstatement nvarchar(4000)
–move declare cursor into sql to be executed
set @sqlstatement = ‘declare cursorName cursor for SELECT * FROM (SELECT StudentId,LetterGrade, CourseCode FROM
vw_TabulationSheetDeptTermWise WHERE TermId = ‘ + @QuoteVariable + @TermId + @QuoteVariable + ‘) ps PIVOT ( MAX(LetterGrade) FOR CourseCode IN (
‘+@cols+’) ) AS pvt’
exec sp_executesql @sqlstatement,@Parameter ,@cols
open cursorName
fetch next from cursorName
into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3, @CourseCode4, @CourseCode5, @CourseCode6,@CourseCode7,@CourseCode8, @CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12
while @@FETCH_STATUS = 0
begin
insert into T_TempReport(StudentId,TermId,Sub1,Sub2,Sub3,Sub4,Sub5, Sub6,Sub7,Sub8,Sub9,Sub10,Sub11,Sub12)
values(@StudentId,@TermId,@CourseCode1,@CourseCode2,@CourseCode3, @CourseCode4,@CourseCode5,@CourseCode6, @CourseCode7,@CourseCode8, @CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12)
fetch next from cursorName
into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3,@CourseCode4, @CourseCode5,@CourseCode6,@CourseCode7, @CourseCode8, @CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12
end
close cursorName
deallocate cursorName
——————————————————————Report Temp Table Data Insertion——————————————————————————
DECLARE @EarnedCredit decimal(8,4),
@WeightedGP decimal(8,4),
@PreviousEarnedCredit decimal(8,4),
@PreviousWeightedGP decimal(8,4),
@TotalEarnedCredit decimal(8,4),
@TotalWeightedGP decimal(8,4),
@AcademicSession varchar(50)
SELECT DISTINCT @EarnedCredit = SUM(T_Common_Course_Information.CourseCredit),
@WeightedGP=SUM(T_Common_Course_Information.CourseCredit*T_Term_Wise_Course_Result.GradePoint)
FROM T_Term_Wise_Course_Result INNER JOIN T_Common_Course_Information ON T_Term_Wise_Course_Result.CourseId = T_Common_Course_Information.CourseId
WHERE T_Term_Wise_Course_Result.TermId=@TermId AND T_Term_Wise_Course_Result.StudentId=@StudentId AND T_Term_Wise_Course_Result.LetterGrade <> ‘F’
SELECT DISTINCT @PreviousEarnedCredit=SUM(T_Common_Course_Information.CourseCredit),
@PreviousWeightedGP=SUM(T_Common_Course_Information.CourseCredit*T_Term_Wise_Course_Result.GradePoint)
FROM T_Term_Wise_Course_Result INNER JOIN T_Common_Course_Information ON T_Term_Wise_Course_Result.CourseId = T_Common_Course_Information.CourseId
WHERE T_Term_Wise_Course_Result.StudentId=@StudentId AND T_Term_Wise_Course_Result.TermId<>@TermId AND T_Term_Wise_Course_Result.LetterGrade <> ‘F’
SELECT DISTINCT @TotalEarnedCredit=SUM(T_Common_Course_Information.CourseCredit),
@TotalWeightedGP=SUM(T_Common_Course_Information.CourseCredit*T_Term_Wise_Course_Result.GradePoint)
FROM T_Term_Wise_Course_Result INNER JOIN T_Common_Course_Information ON T_Term_Wise_Course_Result.CourseId = T_Common_Course_Information.CourseId
WHERE T_Term_Wise_Course_Result.StudentId=@StudentId AND T_Term_Wise_Course_Result.LetterGrade <> ‘F’
SELECT @AcademicSession=AcademicSession from T_Term_Setup WHERE TermId=@TermId
UPDATE T_TempReport SET
EarnedCredit=@EarnedCredit,
WeightedGP=@WeightedGP,
PreviousEarnedCredit=@PreviousEarnedCredit,
PreviousWeightedGP=@PreviousWeightedGP,
TotalEarnedCredit=@TotalEarnedCredit,
TotalWeightedGP=@TotalWeightedGP,
AcademicSession=@AcademicSession
WHERE StudentId=@StudentId
————————————————————————————————————————————————————————-
select Distinct CAST( T_TempReport.StudentId as Varchar(20)) as StudentId,
T_Std_General_Information.StudentsName as StudentName,
T_TempReport.EarnedCredit,T_TempReport.WeightedGP, T_TempReport.PreviousEarnedCredit, T_TempReport.PreviousWeightedGP,
T_TempReport.TotalEarnedCredit,T_TempReport.TotalWeightedGP,T_TempReport.Sub1,
T_TempReport.Sub2,T_TempReport.Sub3,T_TempReport.Sub4,T_TempReport.Sub5,T_TempReport.Sub6,
T_TempReport.Sub7,T_TempReport.Sub8,T_TempReport.Sub9, T_TempReport.Sub10, T_TempReport.Sub11, T_TempReport.Sub12 ,T_TempReport.AcademicSession
from T_TempReport INNER JOIN T_Std_General_Information ON T_TempReport.StudentId=T_Std_General_Information.StudentId
WHERE T_TempReport.TermId = @TermId
end
N3 Technology 