Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zanik99
Starting Member
2 Posts |
 Posted - 2005-11-21 : 15:34:53
|
| I cannot figure a statement for the life of me for this. Please if anyone knows help!I am trying to average numbers from a colum and the numbers are 1 through 5. NOTE: There is a number 6 in this column which represents "N/A". So I am trying to average all the numbers between 1 and 5 and also within a set date range. I can't do it.This is what I have so far. Minus the averaging part.SELECT " & qtn & " FROM tbl_survey WHERE theDate BETWEEN " + Replace(tmys, "'","''") + " AND " + Replace(tmye, "'","''") + ""qtn is a variable for which columntmys = start date and tmye = end date. Like I said I did not do the averaging part yet since I can't figure out how to yank the proper numbers BUT all the rows. Maybe all the numbers that fall between 1 and 5 take up 30 rows but 5 people answred number 6 so there is truly 35 rows. So I need to average the 35 rows over the sum of all the 1-5 answers. Gosh I hope I made sense.Sincerely,Sal |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-21 : 16:01:44
|
| Read up on normalization; you should not have 1 question stored per column in your table: http://www.datamodel.org/NormalizationRules.htmlIn the meantime, just filter out the questions that have an answer of "6" in your SELECT."SELECT " & qtn & " FROM tbl_survey WHERE theDate BETWEEN " + Replace(tmys, "'","''") + " AND " + Replace(tmye, "'","''") + " AND " & qtn & " <> 6" |
 |
|
|
zanik99
Starting Member
2 Posts |
Posted - 2005-11-22 : 08:48:22
|
| Thank you I'll try working some more on this. I saved that link. |
|
|
|
|
|
|
SQL problem with AVG and Date