UFN_MKTSEGMENTATIONFINDERNUMBER_GAPS

create function dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GAPS]
(
  @ID uniqueidentifier = null
)
returns @GAPS table([MIN] bigint primary key, [MAX] bigint)
as
begin
  declare @START bigint;
  select @START = (case when [CHECKDIGIT] = 1 then [LAST_FINDERNUMBER] - 1 else 0 end) from dbo.[MKTFINDERNUMBER];

  --This is broken out into two statements for performance reasons...

  if @ID is null
    insert into @GAPS ([MIN], [MAX])
      select
        [FIRST].[MAX] + 1 as [MIN],
        (
          select min([SECOND].[MIN]) 
          from dbo.[MKTSEGMENTATIONFINDERNUMBER] as [SECOND] 
          where [FIRST].[MAX] < [SECOND].[MIN]
          and [FIRST].[MAX] != [SECOND].[MAX] - 1
        ) - 1 as [MAX]
      from (
        select null as [ID], 0 as [MIN], @START as [MAX]
        union 
        select [ID], [MIN], [MAX] from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [MAX] > @START
      ) as [FIRST]
      where not exists(select * from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [MIN] = [FIRST].[MAX] + 1);
  else
    insert into @GAPS ([MIN], [MAX])
      select
        [FIRST].[MAX] + 1 as [MIN],
        (
          select min([SECOND].[MIN]) 
          from dbo.[MKTSEGMENTATIONFINDERNUMBER] as [SECOND] 
          where [FIRST].[MAX] < [SECOND].[MIN]
          and [FIRST].[MAX] != [SECOND].[MAX] - 1
          and [SECOND].[ID] != @ID
        ) - 1 as [MAX]
      from (
        select null as [ID], 0 as [MIN], @START as [MAX]
        union 
        select [ID], [MIN], [MAX] from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [MAX] > @START
      ) as [FIRST]
      where not exists(select * from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [MIN] = [FIRST].[MAX] + 1 and [ID] != @ID)
      and ([FIRST].[ID] is null or [FIRST].[ID] != @ID);

  return;
end