Calculate a list of values
-
I would like to know how to start a sp that would give me a total calculation.
For example: table name contact2, column uactequiv
select uactequiv from contact2
where uactequiv between 1 and 19
If a return, the score would = 25
select uactequiv from contact2
where uactequiv IN(20,21)
If a return, the score would = 50
select uactequiv from contact2
where uactequiv IN(22,23)
If a return, the score would = 75
How would I begin?
-
I don't fully understand what you want but this will get you going:
select case when uactequiv between 1 and 19 then 25
when uactequiv between 20 and 21 then 50
when uactequiv between 22 and 23 then 75
else 0 end as Score
from contact2
HTH
* Noel -
Thank you so much! That is exactly what I needed
-
How would I calculate the total score at the end of the stored procedure
-
SELECT CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
ELSE 0
END AS Score,
SUM( uactequiv) AS Total
FROM contact2
I wasn't born stupid - I had to study.
-
Hi All,
Question: I am receiving the below error when I added the "update" to the first of my script:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What am I doing wrong?
Script:
update contact2
set udefcon =
(Select
CASE
WHEN key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR') THEN 100 --Add 100 points if key1 has a VALUE in the field
ELSE 0
END +
CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25 -- add points for uactequiv value
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0
END +
CASE
WHEN userdef02 IS NOT NULL THEN 50 -- Add 50 points if userdef02 not null
ELSE 0
END +
CASE
WHEN userdef03 IS NOT NULL THEN 50 -- Add 50 points if userdef03 not null
ELSE 0
END +
CASE
WHEN uconflg = 'Y' THEN 150 --Add 150 points if uconflg has a Y in the field
ELSE 0
END +
CASE
WHEN UVSARRIVDT > ' ' THEN 150 --Add 150 points if UVSARRIDT has a date in the field
ELSE 0
END +
CASE
WHEN uacurank = '1' THEN 50 --Add 50 points if UACURANK has a 1 in the field
ELSE 0
END +
CASE
WHEN uacurank = '2' THEN 30 --Add 30 points if UACURANK has a 2 in the field
ELSE 0
END +
CASE
WHEN uacurank = '3' THEN 20 --Add 20 points if UACURANK has a 3 in the field
ELSE 0
END +
CASE
WHEN uleadcamp = 'Y' THEN 100 --Add 100 points if uleadcamp equals Y
ELSE 0
END +
CASE
when uacadarea1 IN ('BUS', 'BID', 'JOU', 'PSY', 'MED', 'DEN') OR
umajor IN ('ACCT', 'FIN', 'MKTG', 'MGMT', 'BIBL', 'BIBM', 'BIBY', 'BIVM', 'BMCF', 'BMIS', 'BYFM', 'IMC', 'JELM', 'JOUR', 'PSYC') OR
department IN ('PRDE', 'PRME') THEN 50
ELSE 0
END +
CASE
when upsupport = 'Y' THEN 50 --add 50 points if upsupport is marked Y
ELSE 0
END +
CASE
WHEN ubot >' ' THEN 100 --add 100 points if ubot has a VALUE in the field
ELSE 0
END +
CASE
WHEN ugoldlist >' ' THEN 100 --add 100 points if ugoldlist has a VALUE in the field
ELSE 0
END +
CASE
WHEN ufacstaff >' ' THEN 100 --add 100 points if ufacstaff has a VALUE in the field
ELSE 0
END +
CASE
WHEN ualumni = 'Y' THEN 50 --add 50 points if ualumni has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN usibling = 'Y' THEN 100 --add 100 points if usibling has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN uchurchact IN('A', 'B') THEN 50 --add 50 points if uchurchact has an A or B in the field
ELSE 0
END AS TOTALSCORE
FROM
contact2, contact1
WHERE contact1.accountno = contact2.accountno)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply