SQL: sp_CGPA_YGPA_Calculation

USE [dbAsaOld]
GO
/****** Object: StoredProcedure [dbo].[sp_CGPA_YGPA_Calculation] Script Date: 02/18/2013 20:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_CGPA_YGPA_Calculation]
(
@StudentSession varchar(50),
@DepartmentId varchar(20)
)
AS
—- exec sp_CGPA_YGPA_Calculation ‘1986-1990′,’CE’
BEGIN
DECLARE @StudentId varchar(20), @String VARCHAR(100), @TermId VARCHAR(50),  @TermIndex VARCHAR(50),  @CourseId VARCHAR(50), @LetterGrade VARCHAR(5),
@GP VARCHAR(6), @SUBSTR VARCHAR(100), @STRTPOS INT, @CREDIT VARCHAR(10),  @NAME VARCHAR(100), @DEPT VARCHAR(100),@ExamName varchar(100),
@TargetYear varchar(20), @TargetTerm varchar(20),  @AcademicYear varchar(20), @AcademicSession varchar(20), @CourseName varchar(50)

declare @table table
(
Roll varchar(20),
StudentName varchar(100),
[Year] varchar(20),
[Term] varchar(20),
[Session] varchar(50),
CourseNo varchar(50),
CorseTitle varchar(200),
Credit varchar(20),
Grade varchar(20),
GP varchar(20)
)
declare @tstring nvarchar(max), @tblname nvarchar(100)
set @tblname=’tblCGPA’
set @tstring=’create table ‘ + @tblname

declare @tblcgpa table
(
Roll varchar(20),
YGPA1 varchar(20),
YGPA2 varchar(20),
YGPA3 varchar(20),
YGPA4 varchar(20),
CGPAY1 varchar(20),
CGPAY2 varchar(20),
CGPAY3 varchar(20),
CGPAY4 varchar(20),
OldCGPA varchar(20)
)
declare @tblstring nvarchar(max), @tblcgpaname nvarchar(100)
set @tblcgpaname=’tblGPA’
set @tstring=’create table ‘ + @tblcgpaname

declare CGPACalculation cursor for

SELECT StudentId
FROM T_Std_General_Information
WHERE [Session] = @StudentSession AND DepartmentId = @DepartmentId
ORDER BY StudentId

open CGPACalculation

fetch next from CGPACalculation into
@StudentId
while @@FETCH_STATUS = 0
begin

SELECT @NAME = StudentsName FROM T_Std_General_Information  WHERE StudentId=@StudentId

–print @StudentId print @NAME

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;

–print @CourseName

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

–print @String

if(Isnull(@String,”) != ”)
BEGIN
DECLARE @STR VARCHAR(100), @SUBSTR1 VARCHAR(100), @SUBSTR2 VARCHAR(100),  @SUBSTR3 VARCHAR(100),  @SUBSTR4 VARCHAR(100)
SET @STR=@String

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

–print @TermId

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

–print @CourseId

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

–print @CREDIT

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

–print @LetterGrade

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

–print @GP

SELECT @TargetYear=TargetYear,@TargetTerm=TargetTerm,@AcademicYear=AcademicYear, @AcademicSession= AcademicSession FROM T_Term_Setup WHERE TermId=@TermId
DECLARE @CourseCode varchar(20), @CourseTitle varchar(50)
SELECT @CourseCode=CourseCode, @CourseTitle=CourseTitle FROM T_Term_Course_Offer  WHERE CourseId=@CourseId AND TermId=@TermId

–print @TargetYear print @TargetTerm print @AcademicYear print @AcademicSession

INSERT INTO @table(Roll,StudentName,[Year], [Term],[Session],[CourseNo], [CorseTitle],[Credit], Grade,GP)
VALUES(@StudentId,@NAME,@TargetYear, @TargetTerm,@AcademicSession, @CourseCode,@CourseTitle,@CREDIT,@LetterGrade,@GP)

END

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

————————————————— Hard Book Result ——————————————————
DECLARE @PrevResultSummaryY1 varchar(100) = ”, @PrevResultSummaryY2 varchar(100) = ”,  @PrevResultSummaryY3 varchar(100) = ”, @PrevResultSummaryY4 varchar(100) = ”,
@S01 varchar(40) = ”,@S11 varchar(40) = ”, @S21 varchar(40) = ”, @S31 varchar(40) = ”,  @YGPA1 varchar(40) = ”, @CGPA1 varchar(40) = ”,
@S02 varchar(40) = ”,@S12 varchar(40) = ”, @S22 varchar(40) = ”, @S32 varchar(40) = ”,  @YGPA2 varchar(40) = ”, @CGPA2 varchar(40) = ”,
@S03 varchar(40) = ”,@S13 varchar(40) = ”, @S23 varchar(40) = ”, @S33 varchar(40) = ”,  @YGPA3 varchar(40) = ”, @CGPA3 varchar(40) = ”,
@S04 varchar(40) = ”,@S14 varchar(40) = ”, @S24 varchar(40) = ”, @S34 varchar(40) = ”,  @YGPA4 varchar(40) = ”, @CGPA4 varchar(40) = ”

SELECT @PrevResultSummaryY1 = isnull(ResultYear1,”) FROM T_Common_Student_Consolidated_Earned_Credit  WHERE StudentId=@StudentId
SELECT @PrevResultSummaryY2 = isnull(ResultYear2,”) FROM T_Common_Student_Consolidated_Earned_Credit  WHERE StudentId=@StudentId
SELECT @PrevResultSummaryY3 = isnull(ResultYear3,”) FROM T_Common_Student_Consolidated_Earned_Credit  WHERE StudentId=@StudentId
SELECT @PrevResultSummaryY4 = isnull(ResultYear4,”) FROM T_Common_Student_Consolidated_Earned_Credit  WHERE StudentId=@StudentId

if(@PrevResultSummaryY1<>”)
begin
SELECT @TermId = LEFT(@PrevResultSummaryY1,PATINDEX(‘%/%’,@PrevResultSummaryY1)-1)
SET @S01 = RIGHT(rtrim(ltrim(@PrevResultSummaryY1)),LEN(rtrim(ltrim(@PrevResultSummaryY1)))- CHARINDEX(‘/ ‘,rtrim(ltrim(@PrevResultSummaryY1)))) — 121/232/3/3/1st
SET @S11 = RIGHT(@S01,LEN(@S01)-CHARINDEX(‘/’,@S01)) –232/3/3/1st
SET @S21 = RIGHT(@S11,LEN(@S11)-CHARINDEX(‘/’,@S11)) –3/3/1st
SET @YGPA1 = LEFT(@S21,PATINDEX(‘%/%’,@S21)-1)
SET @S31 = RIGHT(@S21,LEN(@S21)-CHARINDEX(‘/’,@S21)) –3/1st
SET @CGPA1 = @S31
end

if(@PrevResultSummaryY2<>”)
begin
SELECT @TermId = LEFT(@PrevResultSummaryY2,PATINDEX(‘%/%’,@PrevResultSummaryY2)-1)
SET @S02 = RIGHT(rtrim(ltrim(@PrevResultSummaryY2)),LEN(rtrim(ltrim(@PrevResultSummaryY2))) -CHARINDEX(‘/’,rtrim(ltrim(@PrevResultSummaryY2)))) — 121/232/3/3/1st
SET @S12 = RIGHT(@S02,LEN(@S02)-CHARINDEX(‘/’,@S02)) –232/3/3/1st
SET @S22 = RIGHT(@S12,LEN(@S12)-CHARINDEX(‘/’,@S12)) –3/3/1st
SET @YGPA2 = LEFT(@S22,PATINDEX(‘%/%’,@S22)-1)
SET @S32 = RIGHT(@S22,LEN(@S22)-CHARINDEX(‘/’,@S22)) –3/1st
SET @CGPA2 = @S32
end

if(@PrevResultSummaryY3<>”)
begin
SELECT @TermId = LEFT(@PrevResultSummaryY3,PATINDEX(‘%/%’,@PrevResultSummaryY3)-1)
SET @S03 = RIGHT(rtrim(ltrim(@PrevResultSummaryY3)),LEN(rtrim(ltrim(@PrevResultSummaryY3)))- CHARINDEX(‘/’,rtrim(ltrim(@PrevResultSummaryY3)))) — 121/232/3/3/1st
SET @S13 = RIGHT(@S03,LEN(@S03)-CHARINDEX(‘/’,@S03)) –232/3/3/1st
SET @S23 = RIGHT(@S13,LEN(@S13)-CHARINDEX(‘/’,@S13)) –3/3/1st
SET @YGPA3 = LEFT(@S23,PATINDEX(‘%/%’,@S23)-1)
SET @S33 = RIGHT(@S23,LEN(@S23)-CHARINDEX(‘/’,@S23)) –3/1st
SET @CGPA3 = @S33
end

if(@PrevResultSummaryY4<>”)
begin
SELECT @TermId = LEFT(@PrevResultSummaryY4,PATINDEX(‘%/%’,@PrevResultSummaryY4)-1)
SET @S04 = RIGHT(rtrim(ltrim(@PrevResultSummaryY4)),LEN(rtrim(ltrim(@PrevResultSummaryY4)))- CHARINDEX(‘/’,rtrim(ltrim(@PrevResultSummaryY4)))) — 121/232/3/3/1st
SET @S14 = RIGHT(@S04,LEN(@S04)-CHARINDEX(‘/’,@S04)) –232/3/3/1st
SET @S24 = RIGHT(@S14,LEN(@S14)-CHARINDEX(‘/’,@S14)) –3/3/1st
SET @YGPA4 = LEFT(@S24,PATINDEX(‘%/%’,@S24)-1)
SET @S34 = RIGHT(@S24,LEN(@S24)-CHARINDEX(‘/’,@S24)) –3/1st

if (@S34 like ‘%/%’)
SET @CGPA4 = LEFT(@S34,PATINDEX(‘%/%’,@S34)-1)
else SET @CGPA4 = @S34

end

INSERT INTO @tblcgpa(Roll,YGPA1,YGPA2,YGPA3,YGPA4, CGPAY1,CGPAY2,CGPAY3, CGPAY4,OldCGPA)
VALUES(@StudentId,@YGPA1,@YGPA2,@YGPA3,@YGPA4, @CGPA1,@CGPA2,@CGPA3,@CGPA4,@CGPA4)
———————————————————————————————————————–

fetch next from CGPACalculation into
@StudentId
end
close CGPACalculation
deallocate CGPACalculation

–SELECT * FROM @table

–SELECT * FROM @tblcgpa

–SELECT t1.Roll,
–t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1, t2.CGPAY2,  t2.CGPAY3, t2.CGPAY4, t2.OldCGPA,
–CAST(ROUND((SUM(cast(t1.Credit as decimal(4,2))*cast(t1.GP as decimal(4,2)))/ SUM(cast(t1.Credit as decimal(4,2)))),2) as decimal(6,2)) as CalculatedCGPA
–FROM @table t1 left join @tblcgpa t2 on t1.Roll=t2.Roll
–WHERE t1.Grade<>’F’ and t1.Grade<>’O’
–GROUP BY t1.Roll,t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1,  t2.CGPAY2, t2.CGPAY3, t2.CGPAY4, t2.OldCGPA

–SELECT * FROM (
–SELECT t1.Roll,
–t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1, t2.CGPAY2, t2.CGPAY3,  t2.CGPAY4, t2.OldCGPA,
–CAST(ROUND((SUM(cast(t1.Credit as decimal(4,2))*cast(t1.GP as decimal(4,2)))/ SUM(cast(t1.Credit as decimal(4,2)))),2) as decimal(6,2)) as CalculatedCGPA
–FROM @table t1 left join @tblcgpa t2 on t1.Roll=t2.Roll
–WHERE t1.Grade<>’F’ and t1.Grade<>’O’
–GROUP BY t1.Roll,t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1, t2.CGPAY2,  t2.CGPAY3, t2.CGPAY4, t2.OldCGPA
–)t WHERE cast(t.OldCGPA as decimal(4,2)) <> cast(t.CalculatedCGPA as decimal(4,2))  AND t.OldCGPA <>” AND t.OldCGPA <>’0.00′

–SELECT t1.Roll,
–t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1, t2.CGPAY2, t2.CGPAY3,  t2.CGPAY4, t2.OldCGPA,
–CAST(ROUND((SUM(cast(t1.Credit as decimal(4,2))*cast(t1.GP as decimal(4,2)))/ SUM(cast(t1.Credit as decimal(4,2)))),2) as decimal(6,2)) as CGPA
–FROM @table t1 left join @tblcgpa t2 on t1.Roll=t2.Roll
–WHERE t1.Grade<>’F’ and t1.Grade<>’O’
–GROUP BY t1.Roll,t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1, t2.CGPAY2, t2.CGPAY3, t2.CGPAY4, t2.OldCGPA

declare @Ptable table
(
Roll int,
YGPA decimal(8,2),
[Year] int
)
declare @Pstring nvarchar(max)
declare @Ptblname nvarchar(100)
set @Ptblname=’YGPA’
set @Pstring=’create table #’ + @Ptblname

insert into @Ptable
(
Roll,
YGPA,
[Year]
)
SELECT t1.Roll,
CAST(ROUND((SUM(cast(t1.Credit as decimal(4,2))*cast(t1.GP as decimal(4,2)))/ SUM(cast(t1.Credit as decimal(4,2)))),2) as decimal(6,2)) as YGPA, [Year]
FROM @table t1
WHERE t1.Grade<>’F’ and t1.Grade<>’O’
GROUP BY t1.Roll, t1.[Year] ORDER BY t1.Roll
–SELECT * FROM @Ptable

declare @Newtable table
(
Roll varchar(10),
YGPA1 decimal(8,2),
YGPA2 decimal(8,2),
YGPA3 decimal(8,2),
YGPA4 decimal(8,2)
)
declare @Newstring nvarchar(max)
declare @Newtblname nvarchar(100)
set @Newtblname=’NewCGPAYGPA’
set @Newstring=’create table #’ + @Newtblname

insert into @Newtable
(
Roll,
YGPA1,
YGPA2,
YGPA3,
YGPA4
)

SELECT Roll, [1] AS YGPA1, [2] AS YGPA2, [3] AS YGPA3, [4] AS YGPA
FROM
(SELECT Roll, [Year], YGPA
FROM @Ptable) p
PIVOT
(
MAX (YGPA)
FOR [Year] IN
( [1], [2], [3], [4] )
) AS pvt
ORDER BY pvt.Roll

declare @Oldtable table
(
Roll varchar(10),
OldYGPA1 varchar(10),
OldYGPA2 varchar(10),
OldYGPA3 varchar(10),
OldYGPA4 varchar(10),
OldCGPA varchar(10),
NewCGPA varchar(10)
)
declare @Oldstring nvarchar(max)
declare @Oldtblname nvarchar(100)
set @Oldtblname=’OldCGPAYGPA’
set @Oldstring=’create table #’ + @Oldtblname

insert into @Oldtable
(
Roll,
OldYGPA1,
OldYGPA2,
OldYGPA3,
OldYGPA4,
OldCGPA,
NewCGPA
)
SELECT t1.Roll,
t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.OldCGPA,
CAST(ROUND((SUM(cast(t1.Credit as decimal(4,2))*cast(t1.GP as decimal(4,2)))/ SUM(cast(t1.Credit as decimal(4,2)))),2) as decimal(6,2)) as CGPA
FROM @table t1 left join @tblcgpa t2 on t1.Roll=t2.Roll
WHERE t1.Grade<>’F’ and t1.Grade<>’O’
GROUP BY t1.Roll,t2.YGPA1, t2.YGPA2, t2.YGPA3, t2.YGPA4, t2.CGPAY1,  t2.CGPAY2, t2.CGPAY3, t2.CGPAY4, t2.OldCGPA
SELECT OT.Roll,OldYGPA1,YGPA1,OldYGPA2 ,YGPA2,OldYGPA3,YGPA3, OldYGPA4,YGPA4,OldCGPA,NewCGPA
FROM @Newtable NT INNER JOIN @Oldtable OT
ON OT.Roll=NT.Roll

SELECT OT.Roll,OldYGPA1,YGPA1,OldYGPA2 ,YGPA2,OldYGPA3,YGPA3,OldYGPA4,YGPA4,OldCGPA,NewCGPA
FROM @Newtable NT INNER JOIN @Oldtable OT
ON OT.Roll=NT.Roll
WHERE OldCGPA<>NewCGPA

SELECT OT.Roll,OldYGPA1,YGPA1,OldYGPA2 ,YGPA2,OldYGPA3,YGPA3,OldYGPA4,YGPA4,OldCGPA,NewCGPA
FROM @Newtable NT INNER JOIN @Oldtable OT
ON OT.Roll=NT.Roll
WHERE OldCGPA<>NewCGPA and OldCGPA is not null

—- exec sp_CGPA_YGPA_Calculation ‘1987-1991′,’CE’
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