quote:

---

Originally posted by TG

Make [survey_date] a datetime column. You can always display the value (on your report frontend) in the desired format. That way you can easily search by date or do "date math" or date ranges.

Be One with the Optimizer
TG

---

create view vw_survey_data
as
select survey_version_id
       ,convert(datetime, 
               replace(
               replace(
               replace(
                     survey_date
              , 'June ', '06/01/')
              , 'September ', '09/01/')
              , 'December ', '12/01/')
        , 101) as survey_date
from   

       (--YOUR TABLE
       select 30 survey_version_id, 'June 2006' survey_date union all
       select 31, 'September 2006' union all
       select 32, 'December 2006'
       ) d
go

DECLARE	@Sample TABLE
	(
		SurveyID INT NOT NULL,
		SurveyDate VARCHAR(50) NOT NULL
	)

INSERT	@Sample
SELECT	26, 'June 2006' UNION ALL
SELECT	27, 'December 2005' UNION ALL
SELECT	28, 'March 2006' UNION ALL
SELECT	31, 'September 2006' UNION ALL
SELECT	32, 'December 2006' UNION ALL
SELECT	33, 'March 2007' UNION ALL
SELECT	34, 'June 2007' UNION ALL
SELECT	35, 'September 2007' UNION ALL
SELECT	36, 'December 2007' UNION ALL
SELECT	40, 'March 2008' UNION ALL
SELECT	41, 'June 2008' UNION ALL
SELECT	43, 'September 2008' UNION ALL
SELECT	44, 'December 2008'

DECLARE	@SurveyID INT

SET	@SurveyID = 50

;WITH cteSource(SurveyID, RecID)
AS (
	SELECT TOP(13)	SurveyID,
			ROW_NUMBER() OVER (ORDER BY SurveyID DESC) AS RecID
	FROM		@Sample
	WHERE		SurveyID <= @SurveyID
	ORDER BY	SurveyID DESC
)
SELECT	SurveyID
FROM	cteSource
WHERE	RecID IN (3, 5, 9, 13)

convert(datetime,Survey_Date) as Survey_Datetime

DECLARE	@Sample TABLE
	(
		SurveyID INT NOT NULL,
		SurveyDate VARCHAR(50) NOT NULL
	)

INSERT	@Sample
SELECT	26, 'June 2006' UNION ALL
SELECT	27, 'December 2005' UNION ALL
SELECT	28, 'March 2006' UNION ALL
SELECT	31, 'September 2006' UNION ALL
SELECT	32, 'December 2006' UNION ALL
SELECT	33, 'March 2007' UNION ALL
SELECT	34, 'June 2007' UNION ALL
SELECT	35, 'September 2007' UNION ALL
SELECT	36, 'December 2007' UNION ALL
SELECT	40, 'March 2008' UNION ALL
SELECT	41, 'June 2008' UNION ALL
SELECT	43, 'September 2008' UNION ALL
SELECT	44, 'December 2008'

WITH cteSource(SurveyID, RecID)
AS (
	SELECT TOP 13	SurveyID,
			ROW_NUMBER() OVER (ORDER BY SurveyID DESC) AS RecID
	FROM		@Sample
	WHERE		SurveyID <= @SurveyID
	ORDER BY	SurveyID DESC
)
SELECT	SurveyID
FROM	cteSource
WHERE	RecID IN (3, 5, 9, 13)

 
Msg 156, Level 15, State 1, Procedure YearVacancyReport, Line 717
Incorrect syntax near the keyword 'WITH'.
Msg 195, Level 15, State 10, Procedure YearVacancyReport, Line 720
'ROW_NUMBER' is not a recognized function name.

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Survey_Version_ID'.

UPDATE    dbo.Survey_Version
SET Survey_Version_ID = 37
WHERE     (Survey_Date = 'March 2008')

quote:

---

Originally posted by Peso

1) ;WITH cteSource(SurveyID, RecID)
2) Set your database to compatibility level 90 or higher for working with the new windowed functions.

---

quote:

---

Originally posted by TimSman

Is Survey_version_id an IDENTITY column? If it is, then I'm pretty certain you won't be able to accomplish what you are trying to do.

---

quote:

---

Originally posted by slmgt

quote:

---

Originally posted by TimSman

Is Survey_version_id an IDENTITY column? If it is, then I'm pretty certain you won't be able to accomplish what you are trying to do.

---

Yes, survey_version-id IS an IDENTITY column. Perhaps a better method would be to either A) create a column of derived data in the table to contain the Survey_Date in DATETIME data type form or B) create an entirely new table to store the DATETIME data. My main concern with regard to this method is how I would have this table populated with data (re: converting the Survey_Date from varchar to datetime for previous instances AND future instances).

I still would prefer to use a self-correcting mechanism to keep the survey_version_id sequential always and related to their respective survey_date.

---

quote:

---

Originally posted by slmgt

Msg 156, Level 15, State 1, Procedure YearVacancyReport, Line 717
Incorrect syntax near the keyword 'WITH'.

---

quote:

---

Originally posted by Peso

quote:

---

Originally posted by slmgt

Msg 156, Level 15, State 1, Procedure YearVacancyReport, Line 717
Incorrect syntax near the keyword 'WITH'.

---

You still can't see the semicolon before the word WITH?

---

quote:

---

Problem(s):
1) Survey_version_id is not sequential with respect to survey_date
2) Survey_version_id is an IDENTITY column
3) Must display desired historical data
a. Traditional Apartment vacancy survey
i. 6mos ago
ii. 1 year ago
iii. 2 years ago
iv. 3 years ago
b. Committee apartment vacancy survey (only valid for Kootenai & Spokane!!!!)
i. Previous 4 quarters
ii. 2 years ago

A) Change survey_version_id to be sequential with respect to their survey_date
a. Must apply to past and future invalid data
b. Problem: Version_ID is IDENTITY column
i. Solution: Unknown
B) Use a search loop to identify the most recent result
a. Most likely a WHILE loop searching for latest survey_version_id
b. Problem: How to find out of sequence data (re: searching 8 quarters back)?
i. Possible solution: A separate FOR loop (or same one) to find data prior to the latest missing data entry
C) Make derived column with converted Survey_Date data to contain Date in DATETIME data type
a. Problem: Where to place column
b. Use new column as a subtraction method (ie: 9/1/2010 – 3 months = 6/1/2010 ? particular survey_version_id)
D) Make derived table
a. Follow guidelines from choice C
E) Search by date instead of ID
a. Problem: Parsing month and year out of input, then searching by PREVIOUS months (ie: if June 2010 is input, search for March 2010)
F) Create view?
a. Problem: Do not know how to do this nor do I know exactly what it is.

---

quote:

---

Originally posted by Peso

The code I posted 10/06/2010 : 19:22:13 executes and runs fine.
You must be doing something with the code that is different from the code I posted.

When I use your code posted 10/11/2010 : 15:51:08, and put back the semicolon the code works as it should.

---

N 56°04'39.26"
E 12°55'05.63"

---

convert(smalldatetime, 'October 2010', 100)

SELECT DATEADD(m, 1, (convert(smalldatetime, 'October 2010', 100)))