I need a little help...

[fishbone34]

Hello everybody, I'm writing a ASP page that pulls data from a database.

I have a column that contains a number value between 0.00 and 100.00.

I need to give a score (1-5) based off of the value above.

Example:
anything <= 9.00 would be a "5"
Anything from 9.01 and 10.00 would be a "4"
Anything from 10.01 and 11.00 would be a "3"
Anything from 11.01 and 12.00 would be a "3"
Anything >= 12.01 would be a "1"

Any help would be wonderful!

This is what I have so far...

'Get Data From M_PhoneSQL
query3 = "Select totalaht FROM m_phonesql where logid = '"&Phone_Id&"' AND DATED = '"&mphonesqldate&"'"
set objRS=Conn.execute(query3)
totalaht = objRS("totalaht")
if totalaht <= 9.00 then
totalaht2 = "5"
end if
if totalaht in [9.01 to 10.00] then
totalaht2 = "4"
end if
if totalaht in [10.01 to 11.00] then
totalaht2 = "3"
end if
if totalaht in [11.01 to 12.00] then
totalaht2 = "2"
end if
if totalaht > 12.00 then
totalaht2 = "1"
end if

[miranda]

you were close but the in statement is sql code not vbscript

if totalaht <= 9.00 then
totalaht2 = 5
elseif totalaht >= 9.01 And totalaht <= 10.00 then
totalaht2 = 4
elseif totalaht >= 10.01 And totalaht <= 11.00 then
totalaht2 = 3
elseif totalaht >= 11.01 And totalaht <= 12.00 then
totalaht2 = 2
else 'it is greater than 12.00
totalaht2 = 1
end if

[fractalvibes]

Select
(CASE
WHEN totalaht <= 9.00 then
'5'
WHEN (totalaht > 9.00 and totalht <= 10.00) then
'4'
WHEN (totalaht > 10.00 and totalht <= 11.00) then
'3'
WHEN (totalaht > 11.00 and totalaht <= 12.00) then
'2'
WHEN totalaht > 12.00 then
'1'
end) as score
FROM m_phonesql
where logid = '"&Phone_Id&"'
AND DATED = '"&mphonesqldate&"'"

Luke - use the SQL....

fv
precise syntax may vary from RDBMS to another...

[fishbone34]

Thank you miranda! It worked like a charm!

[miranda]

you are welcome