April 24, 2002 at 3:55 pm
I am trying to DECLARE / Assign a variable depending on what is in another column. Example, in Column 'abc' if there is a 1, then it gets a 5 assigned in a variable, if there is a 2, then it gets 4 assigned to the variable, if there is a 3, then it gets a 0 assigned to the variable assigned to it. This need to happen to 4 different columns, and then the SUM of the different variables placed into a column named "Total" I have tried using a CASE statement, and IF ELSE Statement. Any assistnace with this would be greatly appreciated
Andrew
Example of CASE Statement
==================================================
USE DatabaseName
GO
SELECT 'Header Name Goes Here' =
CASE
WHEN greet = 1 THEN SET @greet_calc = 5
ELSE @greet_calc = 0
END
FROM TableName
GO
==================================================
Example of IF Statement
===================================================
IF
(SELECT (greet) FROM TableName WHERE greet = 1)
BEGIN
SET @greet_calc = 5
END
ELSE
BEGIN
SET @greet_calc = 0
END
====================================================
How long a minute is....
Depends on what side of the bathroom door you are on.
April 24, 2002 at 4:41 pm
Please post in one area only - we check the topics daily, cross posting just makes it confusing for everyone.
Andy
April 24, 2002 at 4:44 pm
Not 100% sure what you are trying to do here. What it sounds like is you want to return a dataset with a summed total for 4 columns based on a case of conditions in each column. The way you are going about it will not work as you are doing. But so I am sure of what I am doing can you post example of the data and columns, the conditions for each and the expected outcome. Then I will have a better idea of what you need to be doing.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 7:11 am
I have 4 different columns that contain only tinyint, 1,2,3 or 4. This is what I need this to be able to do, not replace the current numbers in the table, but determine the number in a conditional statement, and then with the condition, hold another number in a variable, for each record, and then SUM those values into a 5th column. I am not sure hot that I can add a picture of the table, or I would do that. If in Column named "score1" contains a number 4, relate that number to a score of 15(the score value may change at times, but the 1 thru 4 value will never change) In Column named "score2" contains a value of 3, relate that number to a score of 10, etc... accross all score columns. In a column named "ScoreTotal" add the numbers 15+10 to show 25 in the row for that record. I hope I was able to clearify to assist you. You may e-mail me at aliles@checkmarkinc.com I will then send you some screen snap shots.
Thanks
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
April 25, 2002 at 7:34 am
does this work
SELECT @greet1 = Score1,
@greet2 = score2,
@greet3 = score3,
@greet4 = score4,
@Total = score1 + score2 + score3 + score4
FROM
(
SELECT CASE Score1 WHEN 1 then 5
WHEN 2 then 4
WHEN 3 then 0
END As Score1,
CASE Score2 WHEN 1 then 5
WHEN 2 then 4
WHEN 3 then 0
END As Score2,
CASE Score3 WHEN 1 then 5
WHEN 2 then 4
WHEN 3 then 0
END As Score3,
CASE Score4 WHEN 1 then 5
WHEN 2 then 4
WHEN 3 then 0
END As Score4
FROM ScoreTable
) As Scores
April 25, 2002 at 7:54 am
GRN is right except it doesn't look like you need the variable and you need to handle nulls if you table allows otherwise when you add a null to anything your column is null.
This should be closer
SELECT
score1,
score2,
score3,
score4,
(score1 + score2 + score3 + score4) as TotalScore
FROM
(
/* Note: The reason for else is so a value of 0 is set instead of null, if however fields are not nullable then do 4 position as ELSE 20*/
(CASE score1
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) AS score1,
(CASE score2
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) AS score2,
(CASE score3
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) AS score3,
(CASE score4
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) AS score4
FROM
ScoreTables
) As BaseScores
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 8:36 am
Thank You for your response and assistance. However, this is not working correctly I am afraid. I need this to permanently update the "TotalScore" Column for each record. There is already data in each of the "score#" columns. I am also getting an error around the First CASE statement. I have 3 different people working on this here, adn we cannot figure it out. I feel very bad that I cannot get this. We are at a loss. Thank You again for your assistance. Any help with this problem will be greatly appreciated.
Thanks
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
April 25, 2002 at 8:59 am
Ok here is how you would do the update.
Update ScoreTable SET TotalScore = (
(CASE score1
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) +
(CASE score2
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) +
(CASE score3
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END) +
(CASE score4
WHEN 1 THEN 5
WHEN 2 THEN 10
WHEN 3 THEN 15
WHEN 4 THEN 20
ELSE 0
END)
)
You stated however you are getting an error on the case statment. What is the error please and keep in mind the thens are my own items make sure you change appropriately but the structure should be as is. If you are needing to make other changes what are they and what exactly did you try?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 11:03 am
Thank You very much. It is working very well. I had to massage the code just a little, but it is calculating perfectly.
Thanks Again for all your assistance
Andrew 🙂
How long a minute is....
Depends on what side of the bathroom door you are on.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply