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"

---

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!

---

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