Using outer apply-Point me the Right Direction

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)

Using outer apply-Point me the Right Direction

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-11 : 22:41:11

Have the following data sets with results below (Thanks to visakh16)

declare @t table (X int, Y int, z int)

insert into @t
select 1, 1, 11 union all
select 2, 2, 11 union all
select 3, 3, 11 union all
select 4, 5, 11 union all
select 5, 6, 11 union all
select 6, 7, 11 union all
select 7, 8, 11 union all
select 8, 4, 22 union all
select 9, 6, 22 union all
select 10, 8, 22 union all
select 11, 9, 22 union all
select 12, 3, 33 union all
select 13, 4, 33 union all
select 14, 7, 33 union all
select 15, 9, 55 union all
select 16, 10, 55

select a.x
,a.z
,a.y as Y1
,CASE WHEN a.Y<>b.Y+1 THEN 0
ELSE 1
END AS w

from @t a

outer apply(select top 1 y
from @t
where z = a.z
and x < a.x
order by x desc)b

x z y w
1 11 1 1
2 11 2 1
3 11 3 1 <---This is 1 because from 2 to 3 within
4 11 5 0 Z (11) is continues
5 11 6 1
6 11 7 1
7 11 8 1
8 22 4 1<-----Will like to set this to 0 because
9 22 6 0 from 4 to 6 within same group(22) of Z
10 22 8 0 is not continues(missing 5)
11 22 9 1
12 33 3 1
13 33 4 1
14 33 7 0
15 55 9 1
16 55 10 1

Any assistace on how to update the query to accomodate this will be most welcomed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 23:09:09

[code]declare @t table (X int, Y int, z int)

insert into @t
select 1, 1, 11 union all
select 2, 2, 11 union all
select 3, 3, 11 union all
select 4, 5, 11 union all
select 5, 6, 11 union all
select 6, 7, 11 union all
select 7, 8, 11 union all
select 8, 4, 22 union all
select 9, 6, 22 union all
select 10, 8, 22 union all
select 11, 9, 22 union all
select 12, 3, 33 union all
select 13, 4, 33 union all
select 14, 7, 33 union all
select 15, 9, 55 union all
select 16, 10, 55

select a.x
,a.z
,a.y as Y1
,CASE WHEN a.Y<>ISNULL(b.Y+1,c.Y-1) THEN 0
ELSE 1
END AS w
from @t a
outer apply(select top 1 y
from @t
where z = a.z
and x < a.x
order by x desc)b
outer apply(select top 1 y
from @t
where z = a.z
and x > a.x
order by x)c

output
--------------------------------
x z Y1 w
1 11 1 1
2 11 2 1
3 11 3 1
4 11 5 0
5 11 6 1
6 11 7 1
7 11 8 1
8 22 4 0
9 22 6 0
10 22 8 0
11 22 9 1
12 33 3 1
13 33 4 1
14 33 7 0
15 55 9 1
16 55 10 1[/code]

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-12 : 07:54:29

Thanks ,

I will want w for record 4 and 10 to be 1 because 5 to 6 within z (11) and
8 to 9 within z (22) and both continues

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 08:54:34

quote:
Originally posted by ucal

Thanks ,

I will want w for record 4 and 10 to be 1 because 5 to 6 within z (11) and
8 to 9 within z (22) and both continues

i didnt understand what you're asking here. i gave you solution as per sample output you posted. Are you saying that this is not what you want? if yes, why didnt you illustrate this in sample data posted?

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-12 : 20:41:08

visakh16, I was complicating an otherwise solution you provided.

I am all set, thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:13:04

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources