November 16, 2010 at 1:55 pm
I have three tables. A Fact table (Emp_Facts) which are aggregate counts of the survey data for each Survey_Key and Time_Key. I have a survey table (Survey_EMP) which is a flattened file of all of the surveys. This means there is a distinct Survey_Key for every answer to every question in the survey. Thus if a question has 7 possible answers there are seven different keys. Then I have a time table (TJG_Time) that has a Time_Key for every day of the year. I need a query that will bring back all of the data from the Facts table for all questions and responses but only if when say response 3 was answered for Question 1. Here was my first shot at the query:
SELECT *
FROM EMP_Facts f JOIN Survey_EMP s ON
f.Survey_Key = s.Survey_Key
WHERE (s.Question = 'Q1') AND (S.Response = 3)
Of course I am only getting back the data for that Where clause which I know is wrong:
Survey_Key Time_Key RCount
43 1808 2
43 1809 0
43 1810 6
Where 43 above is my Key from my survey table for that question and response. The time keys are just keys for different days and the Rcount is the aggregate counts of all responses for that day for that question and response.
What I need is as below:
Survey_Key Time_Key RCount
41 1808 3
42 1808 6
43 1808 1
44 1808 2
41 1809 0
42 1809 3
43 1809 10
44 1809 0
etc....
Where 41, 42 and 44 are the other keys for that question but with the other responses (1,2,4). I also need in that result set all of the other corresponding keys for all of the data. Question 2, Response 1, Question 2, Response 2, Question 3, Response 1, etc...
Any help would be greatly appreciated.
November 16, 2010 at 2:07 pm
This seems like it me be getting you towards the path you are looking for??
SELECT *
FROM EMP_Facts f JOIN Survey_EMP s ON
f.Survey_Key = s.Survey_Key
WHERE (s.Question = 'Q1')
AND
exists (SELECT *
FROM EMP_Facts f JOIN Survey_EMP s ON
f.Survey_Key = s.Survey_Key
WHERE (s.Question = 'Q1') AND (S.Response = 3)
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2010 at 2:17 pm
rconway-574047 (11/16/2010)
I have three tables. A Fact table (Emp_Facts) which are aggregate counts of the survey data for each Survey_Key and Time_Key. I have a survey table (Survey_EMP) which is a flattened file of all of the surveys. This means there is a distinct Survey_Key for every answer to every question in the survey. Thus if a question has 7 possible answers there are seven different keys. Then I have a time table (TJG_Time) that has a Time_Key for every day of the year. I need a query that will bring back all of the data from the Facts table for all questions and responses but only if when say response 3 was answered for Question 1. Here was my first shot at the query:
To explain what you're looking for, you want all of the entries for a Time_Key when the Survey_Key that aligns with Question 1 is answered with Response 3 inside of that Time_Key. Is that accurate?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2010 at 2:40 pm
Sean,
Your query basically gave me back what I was already getting. I am only getting back the four keys for that one question. I need to get back all of the keys for every question not just the keys for that one question.
Craig,
That isn't really what I looking for either. This is hard to explain.
I will try it from the user standpoint. The user is looking at a report that has 50 questions with a total of 500 respondents. Question 1 of the survey is "What is your primary shift of work?". This question has 4 possible answers (Day, Evening, Night, Weekend). Let's say "Day" got 312 responses, "Evening" had 50 responses, "Night" had 100 responses and "Weekend" had the other 38 responses. I need to write a query that will return the complete dataset which contains all the questions just for the 312 respondents that answered "Day". So the report would then show "Day" 312 100%, "Evening" 0 0%, "Night" 0 0%, "Weekend" 0 0%. Then question number two thru fifty would also only show its counts for those 312 responses.
November 16, 2010 at 4:02 pm
Can you post some DDL, DML and expected output? This link can help with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
November 16, 2010 at 4:05 pm
rconway-574047 (11/16/2010)
Sean,I will try it from the user standpoint. The user is looking at a report that has 50 questions with a total of 500 respondents. Question 1 of the survey is "What is your primary shift of work?". This question has 4 possible answers (Day, Evening, Night, Weekend). Let's say "Day" got 312 responses, "Evening" had 50 responses, "Night" had 100 responses and "Weekend" had the other 38 responses. I need to write a query that will return the complete dataset which contains all the questions just for the 312 respondents that answered "Day". So the report would then show "Day" 312 100%, "Evening" 0 0%, "Night" 0 0%, "Weekend" 0 0%. Then question number two thru fifty would also only show its counts for those 312 responses.
Bear with the repetitive rephrasing, I'm trying to make sure I understand the requirements. So, your user is staring at a 'initiate report' screen. He goes through a list of the 50 questions, clicking off radio buttons for each question that he wants to see the counts for. If he doesn't check anything, he gets... ? Once he's made his selections, we go through, get a count of each question/answer combination (inside of some timespan, I assume), and you return that result to him?
Or, User sees a screen to initiate the report. He gets to pick *1* question and *1* answer. Once he's done this, he gets a result returned. This result has the count of the requested question. For each person included in that count, we also return counts for all the other question/answer combos.
I think it's the second version but want to make sure.
If it is the second version, you've aggregated to far above the detail you need, which is why you're fighting with it.
I have three tables. A Fact table (Emp_Facts) which are aggregate counts of the survey data for each Survey_Key and Time_Key. I have a survey table (Survey_EMP) which is a flattened file of all of the surveys. This means there is a distinct Survey_Key for every answer to every question in the survey. Thus if a question has 7 possible answers there are seven different keys. Then I have a time table (TJG_Time) that has a Time_Key for every day of the year. I need a query that will bring back all of the data from the Facts table for all questions and responses but only if when say response 3 was answered for Question 1. Here was my first shot at the query:
Survey/survey_key is merely the question/answer file. Time/Time_Key is a calendar item. You have no way of knowing who answered 'Night' and also answered 'I don't like cats'. You've aggregated too far above the detail.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2010 at 12:46 pm
I don't think I am going to get this to work at all with my current aggregate table structure. The aggregate table has no way of associating the other records that I need when I am filtering against that one key There is no field in the aggregate table to associate other like records.
So, I went back to my raw table which has the same basic layout structure except for having one extra field, the "RID" field (Respondent ID). From here I can write a CTE query getting the DISTINCT RID's matching my criteria and the selecting all records that are IN that subquery field:
WITH RIDS as
(
SELECT DISTINCT RID
FROM tblEMPData
WHERE (StmtNum = 17107) AND (Score = 3089)
)
SELECT *
FROM tblEMPData
WHERE RID IN (SELECT RID FROM RIDS)
This gives me all of my records, just not aggregated. I will re-write my lower SELECT statement to do that.
This is all for the purpose of creating a derived table in a Business Objects universe. I am just not sure if a CTE statement will work in that environment yet. We will see.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply