Compare oclumns to another on next row

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)

Compare oclumns to another on next row

Author

Topic

b0p
Starting Member

6 Posts

Posted - 2009-11-13 : 04:59:29

Hello, everyone!
I have some problem I couldn't solve yet. So long story short:
in db:

col col1 col2 col3
1 23 010108 010608
1 33 010508 010109

So basically, I want to check db for some corrupted data. Corruption here is the case when next row col2 [date] will be less than col3 [date]previous row.
I want to check it so, that as a result i'll get col1 in those case where col3 is less than next row col2. Any suggestions/ideas?

Everyday I learn something that somebody else already knew.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 05:02:17

What are the data types of col2 and col3?
In your sample data it looks like varchar...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 05:04:41

And can col1 be used to decide which row is "the next row"?

No, you're never too old to Yak'n'Roll if you're too young to die.

b0p
Starting Member

6 Posts

Posted - 2009-11-13 : 05:14:36

Yes, those col2 and col3 are varchar, date in form of 20010101 yyyymmdd. And I can use this col1 number as next row, in a way next row col1 will be always bigger than previous, but it is not in a common order like 1,2,3 it is like 10,33,40. May be, I can use something like select ROW_NUMBER() over( order by col1 ) AS ROW , * from table, to get this exact row number first. but still, I don't know how to make this comparison. Thanks!

Everyday I learn something that somebody else already knew.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 05:25:43

Your approach was right.
Give this a try:

select dt1.col1 from
(select ROW_NUMBER() over( order by col1 ) AS ROW , * from table)dt1
join
(select ROW_NUMBER() over( order by col1 ) AS ROW , * from table)dt2
on dt2.row = dt1.row-1 and convert(int,dt1.col3) < convert(int,dt2.col2)

No, you're never too old to Yak'n'Roll if you're too young to die.

b0p
Starting Member

6 Posts

Posted - 2009-11-13 : 06:41:08

thanks, this seems to be the right way :)! danke

Everyday I learn something that somebody else already knew.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 07:10:10

Bitte, gern geschehen.

No, you're never too old to Yak'n'Roll if you're too young to die.

b0p
Starting Member

6 Posts

Posted - 2009-11-13 : 09:48:31

Ok, some more coplex query:

How do U think this copmarison-part can be implemented for entire table?
table like this:
col1 | col2 | col3 | col4
6 | 1 | 20100818 | 20110610
6 | 2 | 20110510 | 99999999 <---this will be corrupted row, so I want to get this col1=6(col4>col3 n.row for col1=6)
5 | 5 | 20070707 | 20070808
5 | 7 | 20080707 | 99999999 <--this is normal case for col1=5(since col4<col3 next row)
11 | 10 | 20100818 | 20110604
11 | 18 | 20050101 | 99999999 <--corrupted again

Any suggestions/ideas?
I think, I need to do some procedure here, to make this comparisson-part "for each col1 number"/ cursor.
And of course, I remember that it is friday evening :)

Everyday I learn something that somebody else already knew.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 10:03:16

Sorry but I didn't get that.
In your example data I cannot see a next row.
Mean: No row with col4=99999999 has a next row with the same col1 because the order to decide what is the next row should be col1,col2.

No, you're never too old to Yak'n'Roll if you're too young to die.

b0p
Starting Member

6 Posts

Posted - 2009-11-13 : 10:15:00

So I have this part working ok, but I should put this where col1=6 to check it.
So how can I implement this mechanism to check for every col1 in table, that date from col4 is bigger than col3 next row for this col1. So I should kind a compare those dates for every col1 number. From this upper case: I tkae col1=6 make, comparison and see that col4>col3 date, so i put it to result and get number 6, then I know that for 6, those dates fields are wrong. And i should check it for whole this table.

select dt1.col1 from
(select ROW_NUMBER() over( order by col1 ) AS ROW , * from table where col1=6)dt1
join
(select ROW_NUMBER() over( order by col1 ) AS ROW , * from table where col1=6)dt2
on dt2.row = dt1.row-1 and convert(int,dt1.col3) < convert(int,dt2.col2)

Everyday I learn something that somebody else already knew.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 10:25:40

Back to your first post in this thread.
We talked about: col, col1, col2, col3 and not col1, col2, col3, col4.
So my given solution works on col,col1,col2,col3.
Take this in mind and change the ON-clause to
on dt2.row = dt1.row-1 and convert(int,dt1.col3) < convert(int,dt2.col2) AND dt2.col = dt1.col

I think that is what you need...

No, you're never too old to Yak'n'Roll if you're too young to die.

b0p
Starting Member

6 Posts

Posted - 2009-11-16 : 06:53:23

Thank you, very much, webfred, I'll try that.

Everyday I learn something that somebody else already knew.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources