Filtering data - 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 2000 Forums

Transact-SQL (2000)

Filtering data

Author

Topic

alexc
Starting Member

10 Posts

Posted - 2002-10-16 : 13:01:38

What I am tring to do is return the records where the next record does not fall between a specified range. For example, I would like to know where the values are not between 5 and 10 and return the last record that is good before the value falls out of range.

Example Data:
Row_ID Row_Value
------ ----------
1 6
2 6 <-- Last good value
3 2
4 2
5 7 <-- first good value & Last good value
6 4
7 4
8 6 <-- Last good value
9 10
10 10

Expected Results:
Start_ID End_ID
2 5
5 8

I have some code that is close, but I think my approach is way off track. Any help would be great.

Alex

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-16 : 13:24:50

Man, this is an odd requirement ...

How bout this ...


drop table alexc
go
create table alexc (
    row_id int not null primary key,
    row_value int )
go
insert into alexc (row_id,row_value)
select 1,6
union select 2,6
union select 3,2
union select 4,2
union select 5,7
union select 6,4
union select 7,4
union select 8,6
union select 9,10
union select 10,10
go

select
    start_id,
   (select
        min(row_id)
    from
        alexc a
    where
        row_id > s.start_id and
        row_value between 5 and 10 and
        exists (
            select 1
            from
                alexc
            where
                a.row_id - 1 = row_id and
                row_value not between 5 and 10)) as end_id
from (
    select
        row_id as start_id
    from
        alexc a
    where
        row_value between 5 and 10 and
        exists (
            select 1
            from
                alexc
            where
                a.row_id + 1 = row_id and
                row_value not between 5 and 10) ) s
go

EDIT: or a bit more clearly ...


select
    row_id as start_id,
   (select
        min(row_id)
    from
        alexc b
    where
        row_id > a.row_id and
        row_value between 5 and 10 and
        exists (
            select 1
            from
                alexc
            where
                b.row_id - 1 = row_id and
                row_value not between 5 and 10)) as end_id
from
    alexc a
where
    row_value between 5 and 10 and
    exists (
        select 1
        from
            alexc
        where
            a.row_id + 1 = row_id and
            row_value not between 5 and 10)
go

Jay White
{0}

Edited by - Page47 on 10/16/2002 13:32:42

alexc
Starting Member

10 Posts

Posted - 2002-10-16 : 14:23:23

Thanks for the help, it works great. One more question for you though.

Is there any way to detect if the first record returned is outside of the range?

Row_ID Row_Value
1 4
2 4
3 5 <-- first good record

I know I can not get the row_id for when it went out of range, but I would like to get the row_id when it came back in range.

Thanks.

quote:

Man, this is an odd requirement ...

How bout this ...


drop table alexc
go
create table alexc (
    row_id int not null primary key,
    row_value int )
go
insert into alexc (row_id,row_value)
select 1,6
union select 2,6
union select 3,2
union select 4,2
union select 5,7
union select 6,4
union select 7,4
union select 8,6
union select 9,10
union select 10,10
go

select
    start_id,
   (select
        min(row_id)
    from
        alexc a
    where
        row_id > s.start_id and
        row_value between 5 and 10 and
        exists (
            select 1
            from
                alexc
            where
                a.row_id - 1 = row_id and
                row_value not between 5 and 10)) as end_id
from (
    select
        row_id as start_id
    from
        alexc a
    where
        row_value between 5 and 10 and
        exists (
            select 1
            from
                alexc
            where
                a.row_id + 1 = row_id and
                row_value not between 5 and 10) ) s
go

EDIT: or a bit more clearly ...


select
    row_id as start_id,
   (select
        min(row_id)
    from
        alexc b
    where
        row_id > a.row_id and
        row_value between 5 and 10 and
        exists (
            select 1
            from
                alexc
            where
                b.row_id - 1 = row_id and
                row_value not between 5 and 10)) as end_id
from
    alexc a
where
    row_value between 5 and 10 and
    exists (
        select 1
        from
            alexc
        where
            a.row_id + 1 = row_id and
            row_value not between 5 and 10)
go

Jay White
{0}

Edited by - Page47 on 10/16/2002 13:32:42

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources