Add Value to a column

1

I Have a table where one column has percentage values and another I have with no value named Category

I would like to add a value to the Category column based on the criteria below. any help would be much appreciated.

From To Category
|0.00%| 1.00%| Excellent
|1.00%| 4.99%| Satisfactory
|5.00%| 9.99%| Manageable

Percentages Category
1.00 Excellent
0
1
9.97 Manageable
6.5 Manageable

2 
UPDATE dbo.YourTable
   SET Category = CASE
                  WHEN Percentages >= 0 AND Percentages <  1 THEN 'Excellent'
                  WHEN Percentages >= 1 AND Percentages <  5 THEN 'Satisfactory'
                  WHEN Percentages >= 5 AND Percentages < 10 THEN 'Manageable'
                  ELSE 'UNKNOWN'
                  END
;
2 Likes
3

Thank you so much, for your response I have tried it and that is exactly what I was looking for thanks again.

4

Thank you for the feedback. :+1:

5

Just as a bit of a sidebar, I tend to be very explicit in the code so that there is no doubt even amongst beginners and that's why I tested for each end of each range of values in my previous code.

However and to drive a bit of knowledge home, the WHENs in a CASE statement are always executed in a top down fashion until one matches the stated condition and then it stops.

What that means is that, for this problem, the code can be seriously simplified if it's understood that there will never be a percentage less than 0. Like this...

UPDATE dbo.YourTable
   SET Category = CASE
                  WHEN Percentages <  1 THEN 'Excellent'
                  WHEN Percentages <  5 THEN 'Satisfactory'
                  WHEN Percentages < 10 THEN 'Manageable'
                  ELSE 'UNKNOWN'
                  END
;

Again, I normally resort to the explicit code but this does successfully show that WHENs are executed in a top-down order.

1 Like
6

@JeffModen Your code is crystal clear. What a breath of fresh air.

7

Gosh.. I've "known" you for quite a while, Fred and, coming from you, I'm humbled by your very kind words. Thank you, good Sir.

8

Just one addition - I would use the numeric value in the comparison to make it clearer on what is being compared:

UPDATE dbo.YourTable
   SET Category = CASE
                  WHEN Percentages <  1.00 THEN 'Excellent'
                  WHEN Percentages <  5.00 THEN 'Satisfactory'
                  WHEN Percentages < 10.00 THEN 'Manageable'
                  ELSE 'UNKNOWN'
                  END
;