Computing the averages based on counts

See more:

SQL-Server-2008R2

Need to calculate and assign a value on a scale as follows

The table is as follows :


code name respond1 respond2 respnond3 respond4
-----------------------------------------------------------
001 jane happy satisfied happy neutral
002 paul happy satisfied neutral
003 ama happy happy
004 jon neutral unhappy
005 pet unhappy neutral


select code, name, case
when respond1='happy' then 100
when respond1='satisfactory' then 50
when respond1='neutral' then 25
when respond1='unhappy' then 0
end as "RESULT1",
case
when respond2='happy' then 100
when respond2='satisfactory' then 50
when respond2='neutral' then 25
when respond2='unhappy' then 0
end as "RESULT2",
case
when respond3='happy' then 100
when respond3='satisfactory' then 50
when respond3='neutral' then 25
when respond3='unhappy' then 0
end as "RESULT3",
case
when respond4='happy' then 100
when respond4='satisfactory' then 50
when respond4='neutral' then 25
when respond4='unhappy' then 0
end as "RESULT4",

final table is

code,name,result1,result2,result3,result4,


Request

1. We have some of the respond1, respond2,repsond3, respond4 empty hence in
the process of computing the average they should be eliminated.


code
001 as 4 responds hence the computation of the average should be on 4 counts
002 as 3 responds hence the computation of the average should be on 3 counts
003 as 2 responds hence the computation of the average should be on 2 counts
004 as 2 responds hence the computation of the average should be on 2 counts
005 as 2 responds hence the computation of the average should be on 2 counts

2. If the average of the result1,result2,result3,result4 :

is between 80 and 100 inclusive it should assign the final as 'DELIGHTED'

is between 60 and and less than 80 it should assign the final as 'GOOD'

is between 40 and and less than 60 it should assign the final as 'SATISF'

is between 20 and and less than 40 it should assign the final as 'UNSATIS'

is between 10 and and less than 20 it should assign the final as 'UNHAPPY'

is between 0 and and less than 10 it should assign the final as 'BAD'


How do I accomplish this.

Please assist

Thanks

What I have tried:

This are codes I have worked on using the CASE condition