[Solved] Comparing values from previous 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)

[Solved] Comparing values from previous row

Author

Topic

Quentin
Starting Member

12 Posts

Posted - 2011-04-12 : 00:06:41

I have a table GEOINTERVAL as follows

HOLEID FROM TO VALUE
WB1400UD 0.00 2.54 23
WB1400UD 2.54 6.72 29
.....
WB1400UD 198.05 199.99 21

What I need to be able to do is to check the FROM and TO column of consecutive records to see if there is any GAP between them. They should be consecutive without gaps and I am trying to impliment a data validation script.

Any ideas or suggestions would be appreciated

Thanks

Quentin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-12 : 00:07:51

how would you determine the ordering of the record ? with the FROM column ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-12 : 00:11:43

assuming the validation is per HOLEID and the ordering of record is based on [FROM]


; with data as
(
    select [HOLEID], [FROM], [TO], [VALUE], 
           [ROW_NO] = row_number() over (partition by [HOLEID] order by [FROM])
    from   yourtable
)
select c.*, [GAP] = CASE WHEN c.[TO] = n.[FROM] then 'N' else 'Y' end
from   data c
       inner join data n on  c.[HOLEID] = n.[HOLEID]
                         and c.[ROW_NO] = n.[ROW_NO] - 1

KH
[spoiler]Time is always against us[/spoiler]

Quentin
Starting Member

12 Posts

Posted - 2011-04-13 : 17:45:29

Thats works great.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources