Trying to run a query to identify starts and stops for the gaps and islands in my table. I'm applying a query that I think will work for my data set but I cannot get to run properly. I am getting a conversion error when I run this code:
select start, stop
from (
select m.API_WellNo + 1 as start,
(select min(API_WellNo) - 1
from tblWellMaster x
where x.API_WellNo > m.API_WellNo) as stop
from tblWellMaster m left outer join tblWellMaster r on m.API_WellNo = r.API_WellNo - 1
where r.API_WellNo is null
) as x
where stop is not null;
Here is the error I get: The conversion of the nvarchar value '31003022850000' overflowed an int column.
I can't figure out where this int column is coming from because my API_WellNo is an nvarchar(14)
The number is one of the id's making up the sequence that I am trying to find the gaps/islands in, any help is much appreciated, thank you
Try This:
with cte as (
select start = (cast(m.API_WellNo as bigint) + 1)
, [stop] = ca.[stop]
from tblWellMaster m
cross apply (
select top 1 [stop]=(cast(x.API_WellNo as bigint) -1)
from tblWellMaster x
where x.API_WellNo > m.API_WellNo
order by x.API_WellNo
) as ca
where not exists (
select 1
from tblWellMaster r
where cast(m.API_WellNo as bigint) = (cast(r.API_WellNo as bigint) - 1))
)
select start, [stop]
from cte
where [stop] is not null;