How to solve this Error?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to solve this Error??

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-28 : 10:45:08

I have two tables:

--StudentGrade
if object_id('dbo.StudentGrade') is not null    
drop table dbo.StudentGrade;
create table dbo.StudentGrade(        
StudentGradeID  smallint identity not null,        
Student     varchar(10) not null,        
Course      varchar(10) not null,        
Credit      tinyint not null,        
GradeNo     tinyint not null,        
Remark      varchar(10) null,        
constraint PK_StudentGrade primary key(            
StudentGradeID 
)
);

insert into dbo.StudentGrade(        
Student,        
Course,        
Credit,        
GradeNo,        
Remark
)
select  'Steve', 'ASM01', 3,  50, null union all
select  'Steve', 'ASM01', 3,  43, null union all
select  'Bob',   'ASM01', 3,   0, null union all
select  'Bob',   'OB01',  3,  23, null union all
select  'Bob',   'OB01',  3,  59, null union all
select  'Bob',   'ASM01', 3, 100, null union all
select  'Andy',  'OB01',  3,   0, null union all
select  'Andy',  'ASM01', 3,  50, null union all
select  'Andy',  'ASM01', 3,  10, null union all
select  'Andy',  'OB01',  3,  70, null union all
select  'Andy',  'OB01',  3,   0, 'ABS';

--GradeRate
if object_id('dbo.GradeRate') is not null    
drop table dbo.GradeRate;
create table dbo.GradeRate(        
GradeRateID     tinyint identity not null,        
LowGradeNo      tinyint not null,        
HighGradeNo     tinyint not null,        
Grade           varchar(2) not null,        
Points          tinyint not null,        
constraint PK_GradeRate primary key(            
GradeRateID  
),        
constraint AK_GradeRate_GradeRange unique(            
LowGradeNo,            
HighGradeNo  
),        
constraint AK_GradeRate_Points unique(            
Points  
)
);

insert into dbo.GradeRate(        
LowGradeNo,        
HighGradeNo,        
Grade,        
Points
)
select   0, 34,  'F',  0 union all
select  35, 42,  'D',  1 union all
select  43, 50,  'D+', 2 union all
select  51, 58,  'C',  3 union all
select  59, 66,  'C+', 4 union all
select  67, 74,  'B',  5 union all
select  75, 82,  'B+', 6 union all
select  83, 90,  'A',  7 union all
select  91, 100, 'A+', 8;

Now, I created a T-SQL:

select  Student,        
Course,        
Credit,        
IsAbsent,        
GradeNo,        
Grade,        
Points,        
Quality
from    (        
select  osg.Student,                
osg.Course,                
osg.Credit,                
osg.IsAbsent,                
osg.GradeNo,             
case osg.IsAbsent when 1 then 'ABS' else isnull(gr.Grade, 'X') end as Grade,                
case osg.IsAbsent when 1 then '0' else cast(isnull(gr.Points, 0) as varchar) end as Points,             
case osg.IsAbsent when 1 then 0 else isnull(gr.Points, 0) end * osg.Credit as Quality        
from    (                
select  Student,                        
Course,                        
Credit,                        
case Remark when 'ABS' then 1 else 0 end as IsAbsent,                        
(sum(GradeNo) / count(1)) as GradeNo                
from    dbo.StudentGrade                
group by                        
Student,                        
Course,                        
Credit,                        
case Remark when 'ABS' then 1 else 0 end     
) osg        
left outer join                
dbo.GradeRate gr            
on  osg.GradeNo between gr.LowGradeNo and gr.HighGradeNo    
) CalculatedStudentGrade
order by        
Student,        
Course
compute sum(Credit),        
sum(Quality);

After running the above T-SQL, am getting the following result:

------------------------------------------------------------
Student|Course|Credit|IsAbsent|GradeNo|Grade|Points|Quality
------------------------------------------------------------
Andy	ASM01	 3	  0	 30	 F     0       0
Andy	 OB01	 3	  0	 35	 A     1       3
Andy	 OB01	 3	  1	  0     ABS    0       0
Bob	ASM01	 3	  0	 50	 A     2       6
Bob	 OB01	 3	  0	 41	 A     1       3
Steve	ASM01	 3	  0	 46	 A     2       6
------------------------------------------------------------

But, now the error is:

------------------------------------------------------------
Student|Course|Credit|IsAbsent|GradeNo|Grade|Points|Quality
------------------------------------------------------------
Andy	 OB01	 3	  0	 35	 A     1       3
Andy	 OB01	 3	  1	  0     ABS    0       0
------------------------------------------------------------

Whereas it should be come as:

------------------------------------------------------------
Student|Course|Credit|IsAbsent|GradeNo|Grade|Points|Quality
------------------------------------------------------------
Andy	 OB01	 3	  0	 35	 A     1       3
------------------------------------------------------------

Will you please help me to rectify the erroe, please?

Daipayan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-30 : 17:06:07

Please describe in words why that row shouldn't be in there. I see lots of queries in your post, but I don't see any description of the issue in words.

By the way, saying that it's an error is incorrect. You may not be returning the correct result set, but it is not returning an error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-30 : 17:13:13

Sir,
I had two tables, one where student grade is being recorded by the faculty and other, where marks denomination is being provided, means whenever student get the marks and when I run the T-SQL, by mix-matching the both student grade and marks denomination, it will show the student GPA.
ABS means Absent, So a student called Andy is absent in one part of Exam, so the faculty had marked him ABS, but in other part of exam, he scored marks and henceforth, he scored certain marks and the ABS should not be shown through T-SQL, but whenever am running the T-SQL, ABS is being shown separatly and other of exam marks is being shown separatly in the following way:

------------------------------------------------------------
Student|Course|Credit|IsAbsent|GradeNo|Grade|Points|Quality
------------------------------------------------------------
Andy	 OB01	 3	  0	 35	 A     1       3
Andy	 OB01	 3	  1	  0     ABS    0       0
------------------------------------------------------------

Whereas it should be come as:

------------------------------------------------------------
Student|Course|Credit|IsAbsent|GradeNo|Grade|Points|Quality
------------------------------------------------------------
Andy	 OB01	 3	  0	 35	 A     1       3
------------------------------------------------------------

Hope, I can make you understand my ERROR part!

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-02 : 08:13:52

[code]SELECT sg.Student,
sg.Course,
sg.Credit,
sg.IsAbsent,
sg.GradeNo,
CASE sg.IsAbsent
WHEN 1 THEN 'ABS'
ELSE COALESCE(gr.Grade, 'X')
END AS Grade,
CASE sg.IsAbsent
WHEN 1 THEN 0
ELSE COALESCE(gr.Points, 0)
END AS Points,
CASE sg.IsAbsent
WHEN 1 THEN 0
ELSE COALESCE(gr.Points, 0) * sg.Credit
END AS Quality
FROM (
SELECT Student,
Course,
Credit,
MIN(CASE Remark
WHEN 'ABS' THEN 1
ELSE 0
END) AS IsAbsent,
AVG(1.0E * GradeNo) AS GradeNo
FROM dbo.StudentGrade
GROUP BY Student,
Course,
Credit
) AS sg
LEFT JOIN dbo.GradeRate AS gr ON gr.LowGradeNo <= osg.GradeNo
AND gr.HighGradeNo >= osg.GradeNo
ORDER BY sg.Student,
sg.Course
COMPUTE SUM(sg.Credit),
SUM(Quality)[/code]

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-05-25 : 14:40:18

Sir,

I had did little modification in GradeRate table. I had included Course column, so that I can assign my own high and low grades for each and every courses, which will be different according to courses. For e.g.

ASM01 course may have following graderate:
-----------------------------------------------------------------
GradeRateID | Course | LowGradeNo | HighGradeNo | Grade | Points
-----------------------------------------------------------------
    1       | ASM01  |     0      |     50      |   D   |   0
    2       | ASM01  |    51      |     70      |   C   |   1
    3       | ASM01  |    71      |     85      |   B   |   2
    4       | ASM01  |    86      |    100      |   A   |   4
-----------------------------------------------------------------
OB01 course may have following graderate:
-----------------------------------------------------------------
GradeRateID | Course | LowGradeNo | HighGradeNo | Grade | Points
-----------------------------------------------------------------
    5       |  OB01  |     0      |     50      |   D   |   0
    6       |  OB01  |    51      |     70      |   C   |   1
    7       |  OB01  |    71      |     85      |   B   |   2
    8       |  OB01  |    86      |    100      |   A   |   4
-----------------------------------------------------------------

According to the following graderate, the above given T-SQL will work.
My problem is I am not getting, how should I modify according to this new changes in the table.
Please help me, PLEASE!

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources