Member Avatar for ssreevidya.m

Hai,
I used a table for generating grade for students. for thati used the fields:
UpperBound[int]
LowerBound[int]
Grade [varchar(50)]

I have some datas such as :

UpperBound LowerBound Grade
61 70 D
71 80 C
81 90 B

I sent the value 80.09 to Stored procedure.

Stored procedure something like this:

select * from SCH_Grade_Master where SCH_Grade_Master.Lbound<=80.09 and SCH_Grade_Master.Ubound>=80.09

But no result get for values like 80.09, 70.08 etc
but get results for 81.02 79.00 etc

Can u help me to solve this.

Thanks in advance,

Sreevidya

  • 3 Contributors
  • 2 Replies
  • 185 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for debasisdas

I think the values in the column UpperBound & LowerBound are reversed.

Regarding your SQL, why not use between operator.

Member Avatar for BitBlt

Also, look at your ranges. Since you are using a "float" value to pass in, and your ranges are integers, you have gaps in range coverage. Even if you correct the values for upper and lower (good catch, Debasis!) 80.09 is greater than the upper bound for a C grade, yet still less than the range for a B grade. As long as you have different datatypes for your range and your grade, you'll have this issue. If you only allow integer grades, then you can use your ranges as specified above, and BETWEEN like Debasis suggests.

If you insist on keeping them as different datatypes, you might consider using something like:

-- NB: I'm making up a table name...
-- Lower Upper Grade 
-- 60    70    D 
-- 70    80    C
-- 80    90    B
-- etc.

declare @myGrade float
select @myGrade = 80.09

select * from #grades
where LowerBound <= @myGrade 
and   UpperBound > @myGrade

Note that you can't use the BETWEEN operator, because it is "inclusive" rather than "exclusive". You need ranges that are inclusive at one end and exclusive at the other:
F = (0, 60]
D = (60, 70]
C = (70, 80]
B = (80, 90]
A = (90, 100) <= notice inclusive at both ends!

So you actually have a "special case" at the top of the range.

You have to have extra coding in your WHERE clause. I'll leave it as an exercise to figure out what that would have to look like.

Hope this helps!

Edited by BitBlt because: n/a
commented: agree +13
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.