There are several problems with your procedure and the "WHERE (1 = 1)" clause is not really one of them.

You should always declare the length of any varchar, nvarchar, char...

Thanks Jeff for following this up.

--Andrew

If the column datatype is of character type

STUFF(MyColumn, 6, 0, '-')

If the column datatype is of integer type

STUFF(CAST(MyColumn AS varchar(10)), 6, 0, '-')

Use RANK() instead of ROW_NUMBER()

RANK() OVER(ORDER BY Value) - 1

Assuming a table with this structure:

CREATE TABLE #MyTable (

equipment varchar(20) NOT NULL,

date_start datetime NOT NULL,

date_end datetime NOT NULL

)

And some test data...

Narendra-274001

I just noticed the other thread in the T-SQL 2008 forum.

PLEASE don't cross post - you just waste people's time.

Here's a script for providing data in an easily consumable format. I've made some assumptions about your table structure.

CREATE TABLE #MyTable (

ID INT NOT NULL PRIMARY KEY,

...

Here's an alternative query based on method 2 of my previous post, but uses a recursive CTE instead of a WHILE loop. I haven't compared the various methods for performance.

DECLARE...

This isn't really something that TSQL is well suited for, since the various solutions require an iterative approach. However, if you want to find the modulus of a large integer...

I'm not sure what you are trying to do. If you are trying to copy column values from one table to another where ID columns in the two tables match,...

You need a pivot query. You can use the PIVOT syntax or do something similar to the query below.

I've assumed this table structure, so you will have to adapt the...

Sorry. - I just noticed that this is the Access forum. My comment above assumed it was for SQL Server. However, what I said should hopefully be relevant to Access,...

Don't take those square brackets in the formal syntax description of INSERT in Books Online too literally, but if you insist on using table and column names with spaces (why?),...

Are you interested in 2010 rows only if they match another 2009 row on the Value column? Or are you also interested in 2010 rows that have no matching 2009...

Jeff Moden (12/15/2010)

andrewd.smith (12/15/2010)

Jeff Moden (12/15/2010)

CEILING is the way people...