December 3, 2009 at 10:03 am
Hi,
I need the best way to find the grade based on Percentage Range
in SQL 2005
Percentage Table
LowerPercentage HigherPercentage Grade
0% 40% D
41% 60% C
61% 80% B
81% 100% A
Students Table
StudentId Percentage
10001 75%
10002 45%
10003 11%
10004 86%
Final Output should be
StudentId Grade
10001 B
10002 C
10003 D
10004 A
Please help
December 3, 2009 at 10:28 am
Looks easy. So, what have you tried so far to solve this problem?
December 4, 2009 at 12:47 am
Please try the following, I am also learner, please tell me if any thing wrong in my query, as per my tests it is giving correct results.
Select studentId,
casewhen percentage>=0 and percentage<=40 then 'D'
when percentage>=41 and percentage<=60 then 'C'
when percentage>=61 and percentage<=80 then 'B'
else 'A'
end as Percentage
from #Tempstudent
December 4, 2009 at 1:27 am
Pemmasani Srinivasa Rao. (12/4/2009)
Please try the following, I am also learner, please tell me if any thing wrong in my query, as per my tests it is giving correct results.Select studentId,
casewhen percentage>=0 and percentage<=40 then 'D'
when percentage>=41 and percentage<=60 then 'C'
when percentage>=61 and percentage<=80 then 'B'
else 'A'
end as Percentage
from #Tempstudent
Hi,
You are correct, we also using the case statement, but the value of the grade in the table and not in the string, so we may use the join to archive this easily.
create table #PERCENT
(LP int,HP int,Grade varchar(2))
insert into #PERCENT
select 0,40,'D'
union all
select 41,60,'C'
union all
select 61,80,'B'
union all
select 81,100,'A'
create table #student
(SID int,Perc int)
insert into #student
select 1001,75
union all
select 1002,45
union all
select 1003,11
union all
select 1004,86
select a.SID,a.Perc,b.Grade
from #student a inner join #PERCENT b
on
a.Perc between b.LP and b.HP
December 4, 2009 at 7:11 am
Have to agree, this is how to accomplish this task. Too bad the OP didn't show us what they had tried.
create table dbo.Grade (
LowPercent tinyint,
HighPercent tinyint,
LetterGrade char(1)
);
create table dbo.Student (
StudentID int,
Percentage tinyint
);
insert into dbo.Grade
select 0, 40, 'D' union all
select 41, 60, 'C' union all
select 61, 80, 'B' union all
select 81, 100, 'A';
insert into dbo.Student
select 10001, 75 union all
select 10002, 45 union all
select 10003, 11 union all
select 10004, 86;
select
s.StudentID,
g.LetterGrade
from
dbo.Student s
inner join dbo.Grade g
on (s.Percentage between g.LowPercent and g.HighPercent)
order by
s.StudentID;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply