January 13, 2009 at 1:06 pm
Hi
I'm not sure how to explain this but here goes.
I'm trying to use a lookup table in my database query.
I have a table with several columns. Basically these are answers to questions. I give each person a score for their answers and then a grade of A to E depending on the score. There are 6 multiple choice answers to the five questions and the choices are the same for each answer.
I want to give a score for each answer depending on the choice. e.g.
0 for option 1
1 for option 2
2 for option 3
... upto 5 for option 6.
The scores are then added up and a final score awarded and then a grade given dependant on the score.
I have a table called tblsdstaffsurvey with 5 columns for the answers (all varchar(50)), a score column (float) and a grade column (char(1)).
My 2nd table called Grades has 4 columns. RangeID (int), Floor (decimal), Ceiling (decimal), Grade (char(1).
My 3rd Table called Scores has 3 columns. ScoreID (int), Answer (varchar(50), Score (int).
My query is
SELECT tblsdstaffsurvey.Q1response, tblsdstaffsurvey.Q2response,
tblsdstaffsurvey.Q3response, tblsdstaffsurvey.Q4response, tblsdstaffsurvey.Q5response,
tblsdstaffsurvey.Score, Scores.Score AS Q1Score, Grades.Grade
FROM
tblsdstaffsurvey
LEFT OUTER JOIN Scores
ON
tblsdstaffsurvey.Q1response = Scores.Answer
LEFT OUTER JOIN Grades
ON
tblsdstaffsurvey.Score BETWEEN Grades.Floor AND Grades.Ceiling
I have entered the final score in manually and the grade lookup works.
I want to enter into a new clumn in tblsdstaffsurvey called Q1Score the equivalent value to the answer from the scores table.
When I run the query it only enters the value '0' in the rows where the answer is the first row in the scores table. If the answer is any of the other five it puts a null in the field. It is only looking at the first row of the lookup table Scores.
Hope I have explained this ok and I'm grateful for any assistance
Andy
January 16, 2009 at 7:30 am
Andy, one of your problems seems to be that you have not normalized your tables. The tblsd... one has separate columns for each question. You should try to get that to one row per question which would make things easier.
Since you did not supply test data, if I understand you right the tables look similar to this (if the tblsd... is one row per person? or per??)
use sqlsrvc
go
create table tblsd (Q1 varchar(50), Q2 varchar(50), Q3 varchar(50),
Q4 varchar(50), Q5 varchar(50), score float, grade char(1))
create table grades (rangeid int identity(1,1),floor decimal, ceiling decimal, grade char(1))
create table scores (scoreid int identity(1,1), answer varchar(50), score int)
truncate table tblsd
insert into tblsd (q1,q2,q3,q4,q5,score, grade)
select 'A1','A2','A3','A4','A5',0,'' union all
select 'A2','A3','A4','A5','A1',0,''
truncate table grades
insert into grades (floor,ceiling,grade)
select 0,5,'F' union all
select 6,10,'D' union all
select 11,15,'C' union all
select 16,20,'B' union all
select 21,25,'A' union all
select 26,100,'E'
truncate table scores
insert into scores (answer,score)
select 'A1',0 union all
select 'A2',1 union all
select 'A3',2 union all
select 'A4',3 union all
select 'A5',4 union all
select 'A6',5
select * from tblsd
select * from grades
select * from scores
// ***** tblsd contents
// (q1,q2,q3,q4,q5,score, grade)
// A1 A2A3A4A50.0 ' '
// A2 A3A4A5A10.0 ' '
// ***** grades contents
// (id,floor,ceiling,grade)
// 105F
// 2610D
// 31115C
// 41620B
// 52125A
// 626100E
// ***** scores contents
// (id,answer,score)
// 1A10
// 2A21
// 3A32
// 4A43
// 5A54
// 6A65
Try cross-referencing the Q1-Q5 columns from your tblsd (place them in another table with an Identifier for each - [Questions (int Qid, Question varchar(50)]) and adding an identifier to tie the rows to your former rows.
ex... tblsd(RId int id(1,1), Qid int, score float)
with an additional table if you want to save the value of the grade for each row. The table would be
similar to GradesPerRow (gpwid int ident(1,1), RId int, grade char(1)).
I hope this is clear so far....
Will continue in next post.
January 16, 2009 at 10:14 am
Now there may be a way to do this in a single query but I did not spend the time investigating it. (Let me know if you get it to there).
This is what I mean by doing the cross-referencing and normalizing the tables.
building the tables...
/* Create the new tables */
if exists (select id from sysobjects where type='U' and name = 'tblsd')
drop table tblsd
create table tblsd (tblsdID int identity(1,1),RId int, QId int,
ansID int, score float) --, grade char(1))
if exists (select id from sysobjects where type='U' and name = 'Qref')
drop table Qref
create table Qref (QId int identity(1,1), Q varchar(50))
if exists (select id from sysobjects where type='U' and name = 'grades')
drop table grades
create table grades (rangeid int identity(1,1),floor decimal, ceiling decimal, grade char(1))
if exists (select id from sysobjects where type='U' and name = 'scores')
drop table scores
create table scores (scoreid int identity(1,1), answer varchar(50), score int)
if exists (select id from sysobjects where type='U' and name = 'GBORow')
drop table GBORow -- grades by old rows
create table GBORow (gborID int identity(1,1),RId int, totScore int, Grade char(1))
loading test data...
/* load test data */
truncate table grades -- grades for total scores
insert into grades (floor,ceiling,grade)
select 0,5,'F' union all
select 6,10,'D' union all
select 11,15,'C' union all
select 16,20,'B' union all
select 21,25,'A'
truncate table scores -- scores for answers table
insert into scores (answer,score)
select 'A1',0 union all
select 'A2',1 union all
select 'A3',2 union all
select 'A4',3 union all
select 'A5',4 union all
select 'A6',5
truncate table Qref -- QuestionID to Question ref table
insert into Qref (Q)
select 'Q1' union all
select 'Q2' union all
select 'Q3' union all
select 'Q4' union all
select 'Q5'
truncate table tblsd -- new survey table
insert into tblsd ( RId,QId,ansId,score )
select 1,1,1,0 union all -- Old Row 1
select 1,2,2,0 union all
select 1,3,3,0 union all
select 1,4,4,0 union all
select 1,5,5,0 union all
select 2,1,2,0 union all -- Old Row 2
select 2,2,3,0 union all
select 2,3,4,0 union all
select 2,4,5,0 union all
select 2,5,6,0 union all
select 3,1,1,0 union all -- new Row 3
select 3,2,6,0 union all
select 3,3,6,0 union all
select 3,4,6,0 union all
select 3,5,6,0 union all
select 4,1,6,0 union all -- new Row 4
select 4,2,6,0 union all
select 4,3,6,0 union all
select 4,4,6,0 union all
select 4,5,6,0
truncate table gborow
creating results....
/*
******************************************
** Test tables and data loaded... now to get to work
******************************************
*/
-- determine score for each answer given from old row
update tblsd set score = s.score -- assign a score for each answer
from tblsd t join scores s on ansid = scoreid
-- GBORow gets loaded in subsequent statements
insert into gborow (totscore,rid) -- get the total scores for each of the original rows
select totscore = sum(t1.score),t1.rid
from tblsd t1
join tblsd t2
on t1.rID = t2.rid and t1.tblsdid = t2.tblsdid
group by t1.rid
update gborow set grade = g.grade -- set grade for each of the original rows from total score
from grades g
join gborow g2 on totscore between floor and ceiling
select rid as oldrow, totscore as totalscore, grade from gborow
Toni
January 16, 2009 at 10:20 am
Andy... one more thing. If you set up your test data and tables similar to what I did, you will get an answer much quicker and more completely. Many people will not want to spend the time doing so for you in order to answer your question.
Also.... I know this is a 2005 forum so there is probably a better solution using CTE or such that would allow you to do this with less statements but I don't have access to that right now.
Toni
January 16, 2009 at 12:12 pm
Ok... now for it all in one select (had a bit more time to work it). I was able to remove one of the intermediate holding tables and some columns from others plus added foreign key and primary key
constraints.
Toni
Here's the select....
/*
******************************************
** Run after loading test data (below)
******************************************
*/
select rid as oldrow,grade, totscore as totalscore
from (select sum(s.score) totscore,rid
from tblsd t
join scores s on ansid = scoreid
group by rid) totalscores
join grades g on totscore between floor and ceiling
Table definitions and test data loading...
if exists (select id from sysobjects where type='U' and name = 'tblsd')
BEGIN
Alter table tblsd drop constraint tblsd_QId_FK, tblsd_ansID_FK
drop table tblsd
END
if exists (select id from sysobjects where type='U' and name = 'GBORow')
drop table GBORow -- grades by old rows
if exists (select id from sysobjects where type='U' and name = 'Qref')
drop table Qref
if exists (select id from sysobjects where type='U' and name = 'grades')
drop table grades
if exists (select id from sysobjects where type='U' and name = 'scores')
drop table scores
create table Qref (QId int identity(1,1), Q varchar(50),
CONSTRAINT Qref_PK PRIMARY KEY (QId))
create table grades (rangeid int identity(1,1),floor decimal, ceiling decimal, grade char(1)
,CONSTRAINT grades_PK PRIMARY KEY(rangeid))
create table scores (scoreid int identity(1,1), answer varchar(50), score int
,CONSTRAINT scores_PK PRIMARY KEY(scoreid))
create table tblsd (tblsdID int identity(1,1),RId int, QId int,ansID int
,CONSTRAINT tblsd_QId_FK FOREIGN KEY (QId) REFERENCES Qref(QId)
,CONSTRAINT tblsd_ansID_FK FOREIGN KEY (ansID) REFERENCES scores(scoreid)
,CONSTRAINT tblsd_PK PRIMARY KEY (tblsdid,Rid)
)
insert into grades (floor,ceiling,grade)
select 0,5,'F' union all
select 6,10,'D' union all
select 11,15,'C' union all
select 16,20,'B' union all
select 21,25,'A'
insert into scores (answer,score)
select 'A1',0 union all
select 'A2',1 union all
select 'A3',2 union all
select 'A4',3 union all
select 'A5',4 union all
select 'A6',5
-- QuestionID to Question ref table
insert into Qref (Q)
select 'Q1' union all
select 'Q2' union all
select 'Q3' union all
select 'Q4' union all
select 'Q5'
-- new survey table
insert into tblsd ( RId,QId,ansId)
select 1,1,1 union all -- Old Row 1
select 1,2,2 union all
select 1,3,3 union all
select 1,4,4 union all
select 1,5,5 union all
select 2,1,2 union all -- Old Row 2
select 2,2,3 union all
select 2,3,4 union all
select 2,4,5 union all
select 2,5,6 union all
select 3,1,1 union all -- new Row 3
select 3,2,6 union all
select 3,3,6 union all
select 3,4,6 union all
select 3,5,6 union all
select 4,1,6 union all -- new Row 4
select 4,2,6 union all
select 4,3,6 union all
select 4,4,6 union all
select 4,5,6
January 17, 2009 at 9:34 am
Thanks Toni
I really appreciate the time you've taken to help me out.
I will apply all what you said and let you know how I got on.
Once again thank you ever so much
Andy
January 24, 2009 at 4:40 pm
Thanks once again Toni.
I have put in to practice what you said and all works great. I have tailored it to meet my needs such as change the rid column to the persons name etc.
How can I change the select statement so that instead of adding up the total score of the five questions.it uses the formula (answer1 + answer2 + answer3)/3*answer 4 * answer5.
SELECT rid AS Name, grade, totscore AS totalscore
FROM (SELECT SUM(s.score) totscore,rid
FROM tblsd t
JOIN scores s ON ansid = scoreid
GROUP BY rid) totalscores
JOIN grades g ON totscore BETWEEN FLOOR AND CEILING
Is it just a matter of changing the 'SELECT SUM(s.score)' part of the statement?
I have tried altering this part without any joy.
January 24, 2009 at 11:41 pm
Well that is certainly a challenge Andy. As the new formula (where grades are determined as the average of the score values of the first 3 questions/answers Times the score value of the 4th question/answer times the score value of the 5th question/answer) invalidates your prior Grades table, I used this one for testing.
truncate table grades
insert into grades (floor,ceiling,grade)
select 0,25,'F' union all
select 26,50,'D' union all
select 51,75,'C' union all
select 76,100,'B' union all
select 101,125,'A'
I had to pull data from the table in several stages as different rules are applied depending on the situation. I renamed the old Rid column to SName for clarity and split the task into a joining of the results of three queries where one handles averaging of results for Questions 1-3, another for handling the value of Question 4 and the last for handling the value of Question 5. The results of these were then used to perform the calculation (Avg(Q1thruQ3)*Q4*Q5) then map back to the changed Grades table.
All three queries make use of a derived table for determining the score for each answer so that decisions can be made on how to handle them. (The derived table could be done as a CTE for SQL 2005 and maybe in another way too).
I might play with this a bit more later but the following does provide the expected results as I understand your new formula to dictate. (Anyone else that wants to join in be my guest)
select averages.sname -- name of the person answering the questions
,averages.average * Q4score.score * Q5score.score totscore -- total score from new formula
, grade -- the grade earned by the person
from
/* This query derives the average of answer values for Q1 thru Q3 */
(select avg(score) as average,sname
from (select s.score, t.sname, t.qid
from tblsd t
join scores s on ansid = scoreid) score1
/* score1/2/3 are the same derived table of scores earned per question */
where QID <= 3
group by sname) averages
/* This section determines the value of the answer(s) to Question 4 */
join (select score,sname
from (select s.score, t.sname, t.qid
from tblsd t
join scores s on ansid = scoreid) score2 -- derived table of scores earned
where QID = 4) Q4score
on averages.sname = Q4score.sname
/* This section determines the value of the answer(s) to Question 5 */
join (select score,sname
from (select s.score, t.sname, t.qid
from tblsd t
join scores s on ansid = scoreid) score3
where QID = 5) Q5score
on Q4score.sname = Q5score.sname
/* This section maps the result of the new formula to a grade */
JOIN grades g
ON averages.average * Q4score.score * Q5score.score BETWEEN FLOOR AND CEILING
order by grade -- just to make it pretty (this is not required)
The complexity here (for me at least) is needing to know data in multiple rows and then applying different mathematical operations, aggregation vs multiplication, based on differences with contents of certain columns.
Hope this helps.
Toni
January 25, 2009 at 8:55 am
Once again Toni you have come up with the solution.
I can't tell you how much I appreciate it. I consider myself as a novice and its nice to know that there are experts out there willing to give people like me a helping hand.
I have implemented your suggestion and it works like a dream.
I also tried to modify the query slightly to give me an overall average of everyones totalscore together (A sort of Team average) score and grade. I thought it would be quite simple as all the work was done and all I needed to do was take an average of the results in totscore. I got the error 'Incorrect syntax near ceiling'.
This is what I put:
SELECT AVG(totscore) AS TeamAverageScore, grades as TeamAverageGrade
FROM
(SELECT averages.SName -- name of the person answering the questions
,averages.average * Q4score.score * Q5score.score AS totscore -- total score from new formula
-- the grade earned by the person
FROM
/* This query derives the average of answer values for Q1 thru Q3 */
(SELECT AVG(score) AS average ,SName
FROM (SELECT s.score, t.SName, t.QId
FROM tblsd t
JOIN scores s ON ansID = scoreid) score1
/* score1/2/3 are the same derived table of scores earned per question */
WHERE QId <= 3
GROUP BY SName) averages
/* This selection determines the value of the answer(s) to Question 4 */
JOIN (SELECT score, SName
FROM (SELECT s.score, t.SName, t.QId
FROM tblsd t
JOIN scores s ON ansID = scoreid) score2 -- derived table of scores earned
WHERE QId = 4) Q4score
ON averages.SName = Q4score.SName
/* This section determines the value of the answer(s) to Question 5 */
JOIN (SELECT score,SName
FROM (SELECT s.score, t.SName, t.QId
FROM tblsd t
JOIN scores s ON ansID = scoreid) score3
WHERE QId = 5) Q5score
ON Q4score.SName = Q5score.SName
/* This section maps the result of the new formula to a grade */
JOIN grades g
ON averages.average * Q4score.score * Q5score.score
BETWEEN floor AND ceiling
Just when I thought I was getting the hang of it.
Once again thanks for your help and keep up the good work.
Andy
January 25, 2009 at 12:17 pm
Expert is a highly objective term relative to where one stands - believe me I am not one though there are surely experts by any definition on this site.
Since you have a gift for thinking up new variations, I decided to save the interim results in tables along the way to help make the SQL more readable. When you figure out how you want it all done in the end, you combine these once more for your final SQL.
I split out the former derived table so you can more easily refer to it and added a table for holding the score per person per question and another for the final grade and total score for each person.
With these split out now, I hope it is clearer how to get the answers you want. For instance to get the
average score for the group, you now can do aggregate functions on the group's results and use the result to map back to a grade. Knowing where the information is you should be able to work up whatever queries you might need.
If you need another change, write down in english what you need to do, look at the info you have available, see if it needs to be reorganized or complemented then work at how to access the info to get the results you need. If you think the work you are doing here will be changed frequently, then it might be easier to extract the information then handle the data information through back-end code-level processing where you can use arrays and built-in manipulation routines.
It is an approach you can use in accelerated development where you get it working first, then once it gets the results desired you can go back and optimize your methods.
Best of luck
Toni
if exists (select * from dbo.sysobjects where id = object_id(N'[finalscores]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [finalscores]
GO
/* this will hold the scores per sname per question -- replaces derived table */
CREATE TABLE [finalscores] (
[score] [decimal](18, 0) NOT NULL ,
[sname] [varchar] (30) NOT NULL ,
[Qid] [int] NOT NULL ,
PRIMARY KEY CLUSTERED
(
[Qid],
[sname]
)
)
GO
/* determine the scores per sname per question */
insert into finalscores (score,sname, qid)
select s.score, t.sname, t.qid
from tblsd t
join scores s on ansid = scoreid
/* this table will hold the final grade and total score for each sname */
if exists (select * from dbo.sysobjects where id = object_id(N'[finalgrades]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [finalgrades]
GO
CREATE TABLE [finalgrades] (
[sname] [varchar] (30) NOT NULL ,
[totalscore] decimal,
[finalgrade] char(1),
PRIMARY KEY CLUSTERED
(
[sname]
)
)
GO
/* Determine and load finalgrade and totalscore for each sname */
insert into finalgrades (sname,totalscore, finalgrade)
Select averages.sname -- name of the person answering the questions
,averages.average * Q4score.score * Q5score.score -- total score from new formula
, grade -- the grade earned by the person
from
(select avg(score) as average,sname
from finalscores
where QID <= 3
group by sname) averages
join (select score,sname
from finalscores
where QID = 4) Q4score
on averages.sname = Q4score.sname
join (select score,sname
from finalscores
where QID = 5) Q5score
on Q4score.sname = Q5score.sname
/* This section maps the result of the new formula to a grade */
JOIN grades g
ON averages.average * Q4score.score * Q5score.score BETWEEN FLOOR AND CEILING
/* get group's average score and corresponding grade */
select averages.average, grade
from
(select avg(totalscore) average
from finalgrades fg
join finalscores fs on fs.sname=fg.sname) averages
join grades g
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply