## 5 replies

Last post Nov 22, 2015 10:15 AM by sandeepmittal11

• aliyuusman4u

Member

28 Points

113 Posts

Hello,

First i want to congratulate all members for having a new loo and improved functionality of this site, this is very interesting.

My problem here is , i have table containing student score and  want to compute their Grade , bellow is my code please help me check it

R.FULL_NAME,
R.TOTAL,
CASE WHEN (TOTAL) >= 75
THEN 'A'
WHEN (TOTAL) < 74 AND (TOTAL) > 70
THEN 'AB'
WHEN (TOTAL) < 69 AND (TOTAL) > 65
THEN 'B'
WHEN (TOTAL) < 64 AND (TOTAL) > 60
THEN 'BC'
WHEN (TOTAL) < 59 AND (TOTAL) > 55
THEN 'C'
WHEN (TOTAL) < 54 AND (TOTAL) > 50
THEN 'CD'
WHEN (TOTAL) < 49 AND (TOTAL) > 45
THEN 'D'
WHEN (TOTAL) < 44 AND (TOTAL) > 40
THEN 'E'
ELSE 'F'

HERE IS THE LEGEND

 SCORE GRADE GRADE POINT 75 & Above A 4.00 70-74 AB 3.75 65-69 B 3.50 60-64 BC 3.25 55-59 C 3.00 50-54 CD 2.75 45-49 D 2.50 40-44 E 2.00 Bellow 40 F 0.00

than you

• sandeepmitta...

Contributor

5784 Points

1180 Posts

Better, first create a Grade master table and then use join like below

```CREATE TABLE Grade (
)
select '75 & Above', 'A', 4.00, 75, 100 union all
select '70-74', 'AB', 3.75, 70, 74 union all
select '65-69', 'B', 3.50, 65, 69 union all
select '60-64', 'BC', 3.25, 60, 64 union all
select '55-59', 'C', 3.00, 55, 59 union all
select '50-54', 'CD', 2.75, 50, 54 union all
select '45-49', 'D', 2.50, 45, 49 union all
select '40-44', 'E', 2.00, 40, 44 union all
select 'Bellow 40', 'F', 0.00, 0, 39

inner join Grade G on R.TOTAL between MinVal and Maxval```

Sandeep Mittal | Tech Blog : IT Developer Zone | Twitter : @itdeveloperzone | Facebook : @itdeveloperzone

• aliyuusman4u

Member

28 Points

113 Posts

HELLO SIR,

THIS SEEMS LIKE MAGIC, IT WORKS JUST PERFECT . I PRAY FOR YOU SUCCESS IN ALL YOU DO

THAN YOU

• aliyuusman4u

Member

28 Points

113 Posts

hello sir,

the query works fine but the it seems giving me multiple result . i have just 80 records but when i run it i have 115 records. is like more rows has been introduce

• aliyuusman4u

Member

28 Points

113 Posts

hELLO,

I have just modify my F grade cariteria  to 39 to 0 = F instead of 40 .

than you . problem solved

• sandeepmitta...

Contributor

5784 Points

1180 Posts