Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    Question Unanswered: How to solve this Error??

    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);
    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:

    Code:
    ------------------------------------------------------------
    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:
    Code:
    ------------------------------------------------------------
    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:
    Code:
    ------------------------------------------------------------
    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?
    Last edited by daipayan; 03-30-09 at 18:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •