LearnItFirst User Forum

--Build New StudentGrade Table
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 Date in StudentGrade
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';

--Create GradeRate table
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 date in GradeRate
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;

--T-SQL Query
select  Student,
        Course,
        Credit,
        IsAbsent,
        GradeNo,
        Grade,
        Points,
        Quality,
    Quality/Credit as Aggregate
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(Aggregate) [This part not working],
        sum(Quality);