I need your help in completing this Proc.
I need to calculate the mean score for these values… below is the formulae to calculate the mean.
I.e. if we refer to Q2 in QuestionTable then it should look for D3 column in the AnswerTable.
I had made some progress in capturing the count numbers for one element to calculate mean score… below is the proc. However I unable to proceed further in terms of extending the Proc to calculate all the elements of mean score.
Your help is much appreciated…
Thanks in advance.
Here is a small snippet that demonstrates how to output to a variable using sp_executesql
That should give you the general idea...
answered Apr 21 '10 at 07:01 PM
Matt Whitfield ♦♦
One option might be to write a UDF which adjusts the values appropriately (taking the Strongly Agree and returning 5 for instance) and then simply use the avg function over the udf on that column. That should give you what you want without using Dynamic SQL.
As a general rule, Dynamic SQL is a wonderful thing that lets you do things you couldn't otherwise, but it should be used sparingly only when it is required.
answered Apr 21 '10 at 03:00 PM
Thank you for quick response. Initially I wanted to use UDF however I had shifted to SP because I need to use Dynamic SQL. Just to give you some background... MEAN score is calculated on any column (not specific column) and for any project. So my column name is not constant... hence I had to use dynamic SQL.
I am getting the result but I am not sure on how to save these values into variable to calculate the mean score.
Any help is much appreciated.
Thanks in anticipation.
answered Apr 21 '10 at 06:15 PM