Return results based on a sequence two params

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)

Return results based on a sequence two params

Author

Topic

Bitius
Starting Member

2 Posts

Posted - 2010-06-11 : 21:02:06

I need to filter a query based on two parameters that basically form a sequence. So the user would choose two integer parameters. @int1 and @int2. I would like the data shown below to be filtered so that it only includes the sequences @int1 @int2 in that order ignoring multiples (or any other numbers that could show up).

Original table data would look something like this:

Time | Value
---- -----
1 |1
2 |1
3 |2
4 |2
5 |1
6 |2
7 |2
8 |2
9 |1
10 |2

So if the user selected @in1=1 and @int2=2 the results would be:

Time | Value
---- -----
1 | 1
3 | 2
5 | 1
6 | 2
9 | 1
10 | 2

I hope this makes sense, I'm fairly new to SQL server and couldn't find any examples like this.

Thanks,
Bit

urzsuresh
Starting Member

30 Posts

Posted - 2010-06-12 : 05:46:01

Hello Friend,
Kindly try below one. you will get some idea.

Declare @t table
(
a int,
b int
)
Insert into @t
Select 1,1 union all
Select 2,1 union all
Select 3,2 union all
Select 4,2 union all
Select 5,1 union all
Select 6,2 union all
Select 7,2 union all
Select 8,2 union all
Select 9,1 union all
Select 10,2

declare @s int
declare @res table (time int, value int)
set @s=1
select * from @t
while(select count(*) from @t)>0
Begin
declare @curvalue int
set @curvalue=@s
declare @value int
declare @del int
Select top 1 @value=b,@del=a from @t order by a
if @value=@curvalue
Begin
if @value=1
set @s=2
else
set @s=1
insert into @res values(@del,@value)
End
delete from @t where a=@del
end

Select * from @res

--Let me know that,Do you need any futher clarification

Suri

Sachin.Nand

2937 Posts

Posted - 2010-06-12 : 07:07:05

[code]
select a,b from
(
select * from @t t1 <you can put your filter condition here>
outer apply(select top 1 b as b1 from @t t2 where t1.a=t2.a+1 order by a) t
)t where b<>isnull(t.b1,0)
[/code]

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

PBUH

Bitius
Starting Member

2 Posts

Posted - 2010-06-12 : 11:30:15

Thanks for the quick responses! Both solutions work for the supplied example data set but I need it to work with any two integers the user selects in any sequence. The source table can also contain numbers other than 1s and 2s. The solution from urzsuresh's seems to work for any sequence of integers in either order. Ie if the user wants to select filter by 5 and 2 it would work. Maybe I wasn't clear enough defining the problem, but thanks for the help!

Bit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources