July 18, 2005 at 10:30 am
I was asked to put together a quick, cheap survey for one of our departments. The answers are stored in a table that looks like:
The Q_No links to a table of the actual question text as does Answer. Here's the complication. They decided that they want to separate the results for different groups of people depending on how they answered Question 1 and Question 2, and phrase the results differently when they report it to management.
I had a nice set of rollups all set up that assumed everyone was equal and all they cared about was how many people answered what to each question. Now it appears there are 4 different cases and each has a different number of possible answers based on how you answered Q1 or Q2.
The database is handling it (though had I known up front they were going to do this I would have designed it differently), but my straightforward rollups are worthless. I need to break the results into the four cases. One way I can do that is to change the "question number" in the database. For example, if you answered '1' to question 1 then the next question is 2, but if you answered '3' to question 1 the next question is '22'. Same kind of logic for question 2.
I've alreadly got almost 7,000 responses to the survey so it's not like I can start over. I've been racking my brain trying to come up with an Update query that will modify the results as noted above, that is, if Q_No=2 then if Q_No=1 for the same EmpID set Q_No=22.
Does this make any sense? Any takers?
July 18, 2005 at 11:05 am
Can you elaborate more on this
The Q_No links to a table of the actual question text as does Answer. Here's the complication. They decided that they want to separate the results for different groups of people depending on how they answered Question 1 and Question 2, and phrase the results differently when they report it to management.
Can you also post some sample data, and what you want the results to look like.
Sounds like a bunch of case, and nested case statements.
July 18, 2005 at 11:23 am
They're trying to find out how people feel about our cafeterias. To simplify:
Q1: Do you use the cafeteria?
If Q1 is Yes then Q2: Are you satisfied or dissatisfied?
If satisfied then done, if dissatisfied then Why (8 checkboxes).
If Q1 is No then Why (same 8 checkboxes).
The problem is that Q2 is "Are you satisfied or dissatisfied" if Q1=Yes, but it's "Why not?" if Q1=No. They changed the wording on the web pages, but didn't tell me. I'd have set up a different question (i.e. Q22) for the question following Q1=No, and a the same thing for the answers from Q2: The results from the checkboxes out of Q2 (Q1=Yes, Q2=Dissatisfied) need to be separated from the answers to Q1=No. They're Q3 through Q10 right now for both cases, I'd probably have stored them as Q23-Q30 in the second case.
So an example:
I'd probably be able to get the results I need if it looked like this:
So how to change the Q_No for all where Q_No>2 and Q1 for the same Empid=No?
July 19, 2005 at 6:55 am
Cut and paste the following script into Query Analyzer and execute it. I believe it will give you the results you are looking for. If it does, it should be easy to adapt it for your table and columns.
SET NOCOUNT ON
DECLARE @test-2 TABLE (EmpID int NOT NULL,
Q_No int NOT NULL,
Answer varchar(25) NOT NULL,
PRIMARY KEY CLUSTERED (EmpID, Q_No)
)
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(100, 1, 'Yes')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(100, 2, 'Dissatisfied')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(100, 3, '1')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(100, 5, '1')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(100, 9, '1')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(101, 1, 'No')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(101, 3, '1')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(101, 5, '1')
INSERT INTO @test-2 (EmpID, Q_No, Answer)
VALUES(101, 8, '1')
SELECT * FROM @test-2
UPDATE t2
SET Q_No = t2.Q_No + 20
FROM @test-2 t1 INNER JOIN @test-2 t2 ON t2.EmpID = t1.EmpID
AND t2.Q_No BETWEEN 3 AND 10
WHERE t1.Q_No = 1
AND t1.Answer = 'No'
SELECT * FROM @test-2
July 19, 2005 at 9:49 am
That worked great! Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply