UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOVERLAPPINGRANGES

create function dbo.UFN_DISBURSEMENTPROCESS_RANGESGRID_DONOTALLOWOVERLAPPINGRANGES(@DISBURSEMENTPROCESSID uniqueidentifier, @RANGESGRID xml)
returns bit
with execute as caller
as begin
    declare @ReturnValue bit;
    set @ReturnValue=0;
    declare @TempTable table
    (
    [ID] int
        ,[START] int
    ,[END] int
    ,[COUNT] int
    );
  declare @TOTALDISBURSEMENTS int

  select    
    @TOTALDISBURSEMENTS=COUNT(*)
  from
    dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
    inner join dbo.FINANCIALTRANSACTION as FTD on DPD.ID = FTD.ID
  where
    DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
  group by
    DPD.DISBURSEMENTPROCESSID;

    insert into @TempTable
        select 
      row_number() over (order by [RANGESTART])
            ,[RANGESTART]
      ,[RANGEEND]
      ,case when [RANGEEND]<>0
         then
           [RANGEEND]-[RANGESTART]+1 
         else
           0
         end
       as [RANGECOUNT]      
        from dbo.UFN_DISBURSEMENTPROCESSFORMNUMBERRANGE_FROMITEMLISTXML(@RANGESGRID);

  update @TempTable
  set [COUNT] = @TOTALDISBURSEMENTS - (select SUM([COUNT]) from @TempTable)
  where [COUNT] = 0;    

  if exists
  (
    select * from @TempTable as T1 inner join @TempTable as T2 on T1.ID <> T2.ID
    and
      (
        (
          (T1.[START] <= T2.[START]) 
           and 
          (case when T1.[END]<>0 then T1.[END] else T1.[START]+T1.[COUNT]-1 end >= T2.[START])
        )
        or
        (
          (T1.[START] <= case when T2.[END]<>0 then T2.[END] else T2.[START]+T2.[COUNT]-1 end) 
           and 
          (case when T1.[END]<>0 then T1.[END] else T1.[START]+T1.[COUNT]-1 end >= case when T2.[END]<>0 then T2.[END] else T2.[START]+T2.[COUNT]-1 end)
        )
      )
  )
    set @ReturnValue = 1;

    return @ReturnValue;
end