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???
gogetsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I think this will do:
;WITH 
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
UNION ALL
SELECT b.SurveyID, 'from b to c' as result FROM b INNER JOIN c ON b.SurveyID = c.SurveyID
ORDER BY 2, 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gogetsomeAuthor Commented:
Awesome! That almost works ;-) but for all rows in the table.

How would we limit the search to only

@SurveyNumberThisYear and @SurveyNumberLastYear
0
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.
0
gogetsomeAuthor Commented:
Thank you very much! I figured the filtering... appreciate your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.