Lookup table in SQL server 2008

How do I use a range of values in table

Page 1 of 1

2 Replies - 9666 Views - Last Post: 06 January 2009 - 03:49 PM Rate Topic: -----

#1 Andy Woodward  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-December 08

Lookup table in SQL server 2008

Post icon  Posted 29 December 2008 - 08:44 AM

Hi

I have a database in SQL Server 2008. It has 2 tables (Lookup1 and MyData). the tables are as follows:-

MyData

Column Name - Data Type
Key - int
Name - varchar(64)
Score - float
CorrespondingGrade

Lookup1

Column Name - Data Type
Score - float
CorrespondingGrade - char(1)

I have a UDF called udf_Lookup_CorrespondingGrade.
What I want to do is auto populate the 'CorrespondingGrade' field in MyData with a grade of A to E depending on the value in the Score field.
0 to 18 should give E
18 to 27 should give D
27 to 40 should give C
40 to 100 should give B
100 to 125 should give A

The values I have in Lookup1 are:
Score - CorrespondingGrade
0 - E
18 - D
27 - C
40 - B
100 - A
125 - A

This works fine if the scores are exactly 0,18,27,40,100 or 125. But when the score is between these numbers it produces NULL.

Is it possible to have a range of numbers in the score cell of Lookup1?

In the function field of Computed Column Specification for CorrespondingGrade in MyData I have:-
([dbo].[udf_Lookup_CorrespondingGrade]([Score])).

The UDF looks like this:

USE [TestLookup]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_Lookup_CorrespondingGrade]	Script Date: 12/29/2008 15:37:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[udf_Lookup_CorrespondingGrade] (

@Score float --code to return

) Returns char(1)
AS BEGIN

DECLARE @Result char(1)

SELECT TOP 1
@Result = CorrespondingGrade
FROM Lookup1
WHERE [Score] = @Score

RETURN @Result

END



I hope I've explained everything and given all the information.
Thanks to anyone who can help

Andy

Is This A Good Question/Topic? 0
  • +

Replies To: Lookup table in SQL server 2008

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: Lookup table in SQL server 2008

Posted 29 December 2008 - 08:59 AM

I would probably have two columns: StartingGrade and EndingGrade. Then you could use the "BETWEEN" function for SQL.
Was This Post Helpful? 1
  • +
  • -

#3 Andy Woodward  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-December 08

Re: Lookup table in SQL server 2008

Posted 06 January 2009 - 03:49 PM

Thanks for the reply.

I have now used 2 columns in the lookup table
and used the BETWEEN function in SQL.

It works great. I now want to use the returned grade from the lookup table to give the following result:

A=Totally Satisfied
B=Very Satisfied
C=Satified
D=UnSatisfied
E=Very UnSatisfied

By adding another column to MyData table called 'OutCome'

I tried creating another UDF and another lookup table but got the error:
"Computed column 'CorrespondingGrade' in table 'myData' is not allowed to be used in another computed-column definition" when I tried to add the UDF to the computed column of [OutCome] in MyData table

My code for the UDF is

@Grade char(1) -- Grade to return

) Returns varchar(30)
AS BEGIN

  DECLARE @Result varchar(30)

SELECT TOP 1
  @Result = Outcome
From Lookup2
Where [Grade] = @Grade

Return @Result



lookup2 table is

Grade Outcome
A -Totally Satisfied
B -Very Satisfied
C -Satisfied
D -UnSatisfied
E -Very UnSatisfied

MyData table is

Key Name Score CorrespondingGrade OutCome
1 -Andy -5.74 -E -NULL
2 -Rebecca -12.68 -E -NULL
3 -Violet -42.6 -B -NULL
4 -Eric -74.12 -B -NULL
5 -Tommy -124.22 -A -NULL
1 -Christine -18.667 -D -NULL

Hope this makes sense.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1