I have two tables:
Code:
--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:
Code:
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|Point s|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|Point s|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|Point s|Quality
------------------------------------------------------------
Andy OB01 3 0 35 A 1 3
------------------------------------------------------------

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