Finding consecutive numbers in a table

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)

Finding consecutive numbers in a table

Author

Topic

BMF
Starting Member

4 Posts

Posted - 2010-07-21 : 20:34:50

Hello All,

I have a table that has a series of number values, the goal is to find 3 consecutive numbers in a row regardless if it is ascending or descending order. Once it is found, I want a 0 in the “OSAP” field. Does anyone know how to accomplish this task?
Below is an example table:

TableID COL1 COL2 COL3 COL4 OSAP
1 9 12 54 90
2 52 392 19 2
3 9 8 7 39 0
4 347 547 16 91
5 20 21 22 56 0
6 55 56 57 58 0
7 101 100 99 77 0

From the table above TableID 3, 5,6 and 7 meant the criteria.

Please advice.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-21 : 23:34:24

Use the script,

Create table #Temp1(TableID int,COL1 int,COL2 int,COL3 int,COL4 int,OSAP int)

insert into #Temp1 values(1, 9 ,12 ,54, 90,NULL)
insert into #Temp1 values(2, 52 ,392, 19, 2,NULL)
insert into #Temp1 values(3, 9 ,8 ,7, 39,NULL)
insert into #Temp1 values(4, 347 ,547, 16 ,91,NULL)
insert into #Temp1 values(5, 20, 21 ,22 ,56,NULL)
insert into #Temp1 values(6, 55, 56 ,57 ,58 ,NULL)
insert into #Temp1 values(7, 101, 100, 99 ,77 ,NULL)

update #Temp1 set OSAP=1
from #Temp1 inner join(select TableID,COL1-COL2 as C1,COL2-COL3 as C2,COL3-COL4 as C3 from #Temp1) as T
on T.TableID = #Temp1.TableID
where (T.C1=1 and T.C2=1) or (T.C1=-1 and T.C2=-1) or (T.C1=1 and T.C2=1) or (T.C2=-1 and T.C3=-1)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-21 : 23:42:56

the WHERE clause can be simplified to


where	T.C1	= T.C2
and	T.C1	in (1, -1)

KH
[spoiler]Time is always against us[/spoiler]

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-21 : 23:47:24

thanks for update khtan

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Sachin.Nand

2937 Posts

Posted - 2010-07-22 : 00:26:26

Don't know why I did this.Now it looks complicated for me to.



update t1 set t1.osap=1

from #Temp1 t1 inner join
(
	select TableID,rid from
	(
	  select *,col-dense_rank()over(partition by tableid order by col)as rid from
		(select * from #Temp1)u
		unpivot
		(col for columns in(col1,col2,col3,col4))v
		)t group by TableID,rid
		
	having COUNT(rid)>=3
	
)t on t.TableID=t1.TableID


select * from #Temp1

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources