Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slmgt
Starting Member
37 Posts |
 Posted - 2010-09-29 : 16:21:22
|
I have a stored procedure that contains this line relating to what "survey_version_id" is selected:SET @SurveyID = (select TOP 1 survey_version_id from county_assn where county_id in (SELECT County_ID FROM County WHERE County_Name in (Select CountyValues FROM @Holder)) and survey_version_id < @SurveyID order by survey_version_id desc) Background info: Certain Counties we survey have 100% of all existing survey_version_id (from 1-51). survey_version_id is the primary key, which relates to a specific date/time in our table; it connects surveys with the month/year they were performed. For instance, survey_version_id corresponds to "Survey_Date" of "September 2010". However, not all counties are surveyed every quarter we do surveys, therefore they would not have any data corresponding to survey_version_id 51, but would have data for survey_version_id 50 and 48. @SurveyID contains survey_version_id but is our variable to reflect what survey_version_id we would like the data to reference. In this case, it just goes back 4-5 quarters.Goal: To change my code to reflect 6 months ago, 1 year ago, 2 years ago, and 3 years ago instead of what it currently reflects (3 months ago, 6 months ago, 9 months ago, 1 year ago).How can we best amend this code to reflect our goal?Thank you. |
|
|
slmgt
Starting Member
37 Posts |
Posted - 2010-10-01 : 15:58:22
|
I realized after reading through more of the code that what I provided is not sufficient as there is a WHILE loop that determines what data is represented. So here is all of the code that is likely to be of help:SET @YearBuilt = 'History' SET @HistoryCount = 1 WHILE @HistoryCount < 6 BEGIN -- Below is ORIGINAL history, shows previous 5 quarters IF @HistoryCount < 5 BEGIN SET @SurveyID = (select TOP 1 survey_version_id from county_assn where county_id in (SELECT County_ID FROM County WHERE County_Name in (Select CountyValues FROM @Holder)) and survey_version_id < @SurveyID order by survey_version_id desc) END ELSE BEGIN SET @SurveyID = @SurveyID - 4 END My intention was to set 4 different IF statements to reflect each HistoryCount, then use the ELSE statement to represent the 5th possibility [if I even use a 5th]. So now that I provided more useful information, what can I do to get the data I would like or is there anything else you need?Thank you in advance! |
 |
|
|
|
|
|
|
|
Stored Procedure IF statements or CASE?