check column consistency - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

check column consistency

Author

Topic

sudhirbharti
Starting Member

16 Posts

Posted - 2009-03-26 : 06:48:22

i have two column in a table like following
col1 col2
0 0
0 5
0 10
0 15
1 0
1 5
1 10
1 15

now what my requirement is to check the interval of 5 in col2, if the sequence is not matched in col2 like this

col1 col2
0 0
0 5
0 11
0 15
1 0
1 3
1 11
1 4

then it update the col2 on interval of 5. like this
col1 col2
0 0
0 5
0 10
0 15
1 0
1 5
1 10
1 15

Any Ideas?

matty
Posting Yak Master

161 Posts

Posted - 2009-03-26 : 07:34:46

declare @t table
(
col1 int,
col2 int
)
insert @t
select 0, 0 union all
select 0, 5 union all
select 0, 11 union all
select 0, 15 union all
select 1, 0 union all
select 1, 3 union all
select 1, 11 union all
select 1, 4

SELECT a.col1,rownum * 5
FROM
(
SELECT col1,row_number() OVER(PARTITION BY col1 ORDER BY col1) AS rownum FROM @t
)a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources