Topic awaiting preservation: SQL Question.... (Page 1 of 1)

Currently, I have an SQL statement as follows

SELECT 

SUM(CASE Response WHEN '0' THEN 1 END) AS NA, 

SUM(CASE Response WHEN '1' THEN 1 END) AS Bad, 

SUM(CASE Response WHEN '2' THEN 1 END) AS OK, 

SUM(CASE Response WHEN '3' THEN 1 END) AS Good, 



survey_question.question_text, survey_response.question_id FROM survey_response INNER JOIN survey_question ON survey_response.question_id = survey_question.question_id WHERE (survey_response.sprint_id = @sprint_id@) GROUP BY survey_question.question_text, survey_response.question_id ORDER BY survey_response.question_id

@sprint_id@ is the sprint I want to get the question results for.

This works fine and well, however just got tossed a new requirement - they want to be able to compare the last 3 sprints.

So, for the current query, the resulting record set looks approx like.

1	1	1	3	Pre Planning	1

	1	1	1	3	Planning	                2

What I need to do, is get a result table that looks like

1,3,1	1,1,0	1,5,0	3,3,0	Pre Planning	1

	1,5,6	1,2,0	1,3,1	3,N,N	Planning	                2

where the N,N are the sum's from the previous two sprint IDs.

The table structure looks like

The tables/structures are as follows.

sprint:
sprint_id : varchar(4)
start_date: datetime
end_date : datetime

survey_question:
question_id: int allow-nulls - no, identiy, identity
seed 1, identiy increment 1
question_text: varchar(200)

survey_response:
response_id: int allow-nulls - no, identiy, seed 1,
identiy increment 1
question_id: int
sprint_id: varchar(4)
name: varchar(50)
response: int

any ideas/suggestions?

umm not outside this should be in SSS.



.:[ Never resist a perfect moment ]:.