Complex Data Comparism - 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)

Complex Data Comparism

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-07 : 21:05:27

I have a table with the following data elements

X Y Val1
-- -- ----
1 1 20
2 1 10
3 1 10
4 1 20
5 2 15
6 2 17
7 2 15
8 3 18
9 3 18
10 3 18
11 4 20
12 4 23
13 4 10

Looking for a query that will return the followind data elements.

X Y Val1 Val2
-- -- ---- ----
1 1 20 0
2 1 10 10
3 1 10 10
4 1 20 0
5 2 15 0
6 2 17 0
7 2 15 0
8 3 18 18
9 3 18 18
10 3 18 18
11 4 20 0
12 4 23 0
13 4 10 0

Val2 is 0 if within the same group of Y ,Val1 is not the same as
the next value of Val1 ,otherwise Val1 =Val2'

Thus :
IF Y = Y +1 and Val1 <> Val1 +1 THEN Val2 = 0
ELSE Val2 =Val1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-11-07 : 23:11:54

I'm not sure your desired results matches your requirement description. But here is a way to self join so that you can compare one value with the next value. As you can see my output doesn't match yours so you may need to tweek the CASE statement. I also included the "nextVal1" column so you could see what was being compared.


declare @t table (X int, Y int, Val1 int)
insert @t
select 1, 1, 20 union all
select 2, 1, 10 union all
select 3, 1, 10 union all
select 4, 1, 20 union all
select 5, 2, 15 union all
select 6, 2, 17 union all
select 7, 2, 15 union all
select 8, 3, 18 union all
select 9, 3, 18 union all
select 10, 3, 18 union all
select 11, 4, 20 union all
select 12, 4, 23 union all
select 13, 4, 10

select a.x
       ,a.y
       ,a.val1
       ,b.val1 [NextVal1]
       ,case
              when  a.Val1 <> isNull(b.Val1,-1) THEN 0
              ELSE a.Val1 
              end as [val2]

from @t a
left outer join @t b 
       on b.y = a.y --within same Y group
       and b.x-1 = a.x --self join to "next" value as defined by X sequence

OUTPUT:
x           y           val1        NextVal1    val2
----------- ----------- ----------- ----------- -----------
1           1           20          10          0
2           1           10          10          10
3           1           10          20          0
4           1           20          NULL        0

5           2           15          17          0
6           2           17          15          0
7           2           15          NULL        0

8           3           18          18          18
9           3           18          18          18
10          3           18          NULL        0

11          4           20          23          0
12          4           23          10          0
13          4           10          NULL        0

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-07 : 23:27:26

The criteria you mentioned does not match your results.

here is what you described

quote:

Val2 is 0 if within the same group of Y ,Val1 is not the same as
the next value of Val1 ,otherwise Val1 =Val2'


Declare @Mytable table (x int,y int,val1 int)

insert into @Mytable(x,y,val1)
select 1, 1, 20 Union All
select 2, 1, 10 Union All
select 3, 1, 10 Union All
select 4, 1, 20 Union All
select 5, 2, 15 Union All
select 6, 2, 17 Union All
select 7, 2, 15 Union All
select 8, 3, 18 Union All
select 9, 3, 18 Union All
select 10, 3, 18 Union All
select 11, 4, 20 Union All
select 12, 4, 23 Union All
select 13, 4, 10



	Select 
	a.x
	,a.y
	,a.Val1
	,	case 
		when b.y is null then 0
		when  a.y = b.y and a.Val1 <> b.val1 then 0
		else a.val1
		end as Val2
	from
	@mytable a
	Left Join
	@mytable b 
	on a.x = b.x + 1

Here is the query if you want to 0 it out if Y is not = to the next row


Declare @Mytable table (x int,y int,val1 int)

insert into @Mytable(x,y,val1)
select 1, 1, 20 Union All
select 2, 1, 10 Union All
select 3, 1, 10 Union All
select 4, 1, 20 Union All
select 5, 2, 15 Union All
select 6, 2, 17 Union All
select 7, 2, 15 Union All
select 8, 3, 18 Union All
select 9, 3, 18 Union All
select 10, 3, 18 Union All
select 11, 4, 20 Union All
select 12, 4, 23 Union All
select 13, 4, 10



	Select 
	a.x
	,a.y
	,a.Val1
	,	case 
		when b.y is null then 0
		when  a.y = b.y and a.Val1 <> b.val1 then 0
		when  a.y <> b.y then 0
		else a.val1
		end as Val2
	from
	@mytable a
	Left Join
	@mytable b 
	on a.x = b.x + 1

please clarify what you want, the results you posted do not match your request.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-07 : 23:28:53

I hate when I don't refresh my screen!!! :)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 00:50:18

Not sure if you really meant this


select a.x
       ,a.y
       ,a.val1
       ,case
              when  a.Val1 = isNull(b.Val1,-1) 
                    OR a.Val1 = isNull(c.Val1,-1)
              THEN a.Val1 
              ELSE 0
              end as [val2]

from @t a
left outer join @t b 
       on b.y = a.y 
       and b.x-1 = a.x
left outer join @t c
       on c.y=a.y
       and c.x+1=a.x

this will give you desired sample output. But obviously your description doesnt suit this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 00:54:37

And just in case the values of x doesnt follow continuosly (ie. you may have gaps due to deletion). then use below


select a.x
       ,a.y
       ,a.val1
       ,b.val1
       ,c.val1
       ,case
              when  a.Val1 = isNull(b.Val1,-1) 
                    OR a.Val1 = isNull(c.Val1,-1)
              THEN a.Val1 
              ELSE 0
              end as [val2]
from @t a
outer apply(select top 1 Val1
            from  @t 
            where y = a.y 
            and x > a.x
            order by x)b
outer apply(select top 1 Val1
            from  @t 
            where y = a.y 
            and x < a.x
            order by x desc)c

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-11-08 : 08:45:45

Great thanks to TG,Vinnie881 and visakh16.

visakh16, your solution got me to where I wanted to be.

Now I want to adjust your code to address his situation.

Cosider the table below.

ID X Y
-- -- --
1 a 11
2 a 12
3 a 14

4 b 10
5 b 16

6 c 7
7 c 8

The Y values of record 3 and 5 are out of sequence . They should have been 13 and 11 respectively.The idea is to Flag cases that are out of sequence within the same group of Y

My desired result is as follows

ID X Y Y2 Flag
-- -- -- -- ----
1 a 11 11 1
2 a 12 12 1
3 a 14 13 0

4 b 10 10 1
5 b 16 11 0

6 c 7 7 1
7 c 8 8 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources