Find gaps in sequence - 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

General SQL Server Forums

New to SQL Server Programming

Find gaps in sequence

Author

Topic

the_roof
Starting Member

2 Posts

Posted - 2012-04-27 : 23:41:11

Hello so I looking for help how to find a gap in sequence with two different columns? The values are startbates and endbates

thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-27 : 23:47:38

can you post some sample data and the expected result ?

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

the_roof
Starting Member

2 Posts

Posted - 2012-04-28 : 00:12:11

Begcontrol Opposingstartbates opposingendbates
FXGFluegel000000001 FXGFluegel000000001 FXGFluegel000000001
FXGFluegel000000002 FXGFluegel000000002 FXGFluegel000000002
FXGFluegel000000003 FXGFluegel000000003 FXGFluegel000000003
FXGFluegel000000004 FXGFluegel000000004 FXGFluegel000000004
FXGFluegel000000005 FXGFluegel000000005 FXGFluegel000000006
FXGFluegel000000007 FXGFluegel000000007 FXGFluegel000000008
FXGFluegel000000009 FXGFluegel000000009 FXGFluegel000000009

Want to find the gaps. There are 604085 rows of data.
Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 00:14:13

and your expected result please.

Also explain how do you consider "gap" based on which columns ?

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-28 : 06:32:09

How do you define "gaps" and which columns are we looking into??

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-28 : 18:54:49

quote:
Originally posted by vinu.vijayan

How do you define "gaps" and which columns are we looking into??

N 28° 33' 11.93148"
E 77° 14' 33.66384"

it would be much better if you can try posting some solutions also rather than simply repeating what others have already suggested.
I'm seeing this happening frequently of late!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-04-29 : 19:57:08

quote:
Originally posted by the_roof

Begcontrol Opposingstartbates opposingendbates
FXGFluegel000000001 FXGFluegel000000001 FXGFluegel000000001
FXGFluegel000000002 FXGFluegel000000002 FXGFluegel000000002
FXGFluegel000000003 FXGFluegel000000003 FXGFluegel000000003
FXGFluegel000000004 FXGFluegel000000004 FXGFluegel000000004
FXGFluegel000000005 FXGFluegel000000005 FXGFluegel000000006
FXGFluegel000000007 FXGFluegel000000007 FXGFluegel000000008
FXGFluegel000000009 FXGFluegel000000009 FXGFluegel000000009

Want to find the gaps. There are 604085 rows of data.
Thanks!

Maybe this is what you're looking for


select substr(max(b.opposingendbates),1,10)+right('00000000'+convert(varchar,cast(substr(max(b.opposingendbates),10,9) as int)+1),9) as from_missing
      ,a.to_missing
  from (select substr(a.opposingstartbates,1,10)+right('00000000'+convert(varchar,cast(substr(a.opposingstartbates,10,9) as int)-1),9) as to_missing
          from table as a
               left outer join table as b
                       on b.opposingendbates=substr(a.opposingstartbates,1,10)+right('00000000'+convert(varchar,cast(substr(a.opposingstartbates,10,9) as int)-1),9)
         where b.begcontrol is null
       ) as a
       left outer join table as b
                    on b.opposingendbates<a.to_missing

I know its not pretty (and maybe also very slow), but it might work.

This has not been tested for type/syntax error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources