• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Help with select statement

Hello, I have a table called Surveys:

id int,
SurveyId int,
Year varchar(4),
Status varchar(1),
Condition varchar(500)

There many be many rows per surveyId.
I will pass in two parameters: @SurveyIdThisYear and @SurveyIdLastYear.
Status can be a, b or c.

My statement needs to return all rows where status went from

from b to c
form a to b

How can I accomplish this???
  • 3
1 Solution
I think this will do:
a AS
(SELECT DISTINCT SurveyID FROM Surveys WHERE Status = 'a'),
b AS
(SELECT DISTINCT SurveyID FROM Surveys WHERE Status = 'b'),
c AS
(SELECT DISTINCT SurveyID FROM Surveys WHERE Status = 'c')
SELECT a.SurveyID, 'from a to b' as result FROM a INNER JOIN b ON a.SurveyID = b.SurveyID
SELECT b.SurveyID, 'from b to c' as result FROM b INNER JOIN c ON b.SurveyID = c.SurveyID

Open in new window

gogetsomeAuthor Commented:
Awesome! That almost works ;-) but for all rows in the table.

How would we limit the search to only

@SurveyNumberThisYear and @SurveyNumberLastYear
gogetsomeAuthor Commented:
Sorry, to make things confusing, but I forgot to mention SurveyNumber also belongs to the table and is the primaryKey.

@SurveyNumberThisYear and @SurveyNumberLastYear  =  this years surveynumber and last years survey number.

The user will select the two survey numbers and the statement will return the differences between the surveys based on surveynumber and the difference in status.

Hope this helps clear up my mistake.
gogetsomeAuthor Commented:
Thank you very much! I figured the filtering... appreciate your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now