declare @start_time datetime, @stop_time datetime   
print @start_time   
declare @dopusk float   
set DATEFORMAT dmy   
set @dopusk=200 -- Допустимые метры разницы между пробегом по одометру и расстоянием между коорд - если больше - выводить инфо   
set @start_time='01-11-2011 00:00:00' -- время начала запроса   
set @stop_time='11-11-2011 23:59:59' -- время окончания запроса   

declare @t_id int,         
         @t_garag_number varchar(16),         
         @t_gos_number varchar(16),         
         @d_code varchar(16)   

declare cur_transport cursor local read_only for   
         select t.ObjectID, t.ObjectGarNum, t.ObjectGosNum, d.code         
         from bn.dbo.FLEET_Object as t         
         inner join bn.dbo.SYS_DEV_Device as d on d.id=t.Device_id and d.DeviceActive=1   
         --inner join bn.dbo.SYS_DEV_CurrentData as CD on CD.ObjectID=t.ObjectID   
      where t.ObjectGarNum like '%418%' -- фильтр по гаражному номеру для ускорения   
         order by t.ObjectGarNum;   

declare @error_num int   
declare @long float, -- тек переменные из курсора cur_AD   
         @lat float,         
         @tele_time datetime,         
         @analog_data float,         
         @CURRENT_MILAGE int,   
         @valid char(1)   

declare @pr_long float, -- предыдузие переменные из курсора cur_AD   
         @pr_lat float,         
         @pr_tele_time datetime,         
         @pr_analog_data float,         
         @pr_CURRENT_MILAGE int,   
         @pr_valid char(1)   

declare @len_on_coord float, @len_on_mil float;   

set @error_num=0;   

if (not object_id('tempdb..#temp') IS NULL)         
drop table #temp   

create table #temp (   
         error_num int,   
         objectid int,
         garag_num varchar(16),   
         gos_num varchar(16),   
         dev_code varchar(16),   
         long float,   
         lat float,   
         tele_tme datetime,   
         analog_data float,   
         CUR_MILAGE int,   
         valid char(1),   
         len_on_coord float, len_on_mil float   
)   

print 'open cur_transpor'  
open cur_transport   
fetch next from cur_transport into @t_id, @t_garag_number, @t_gos_number, @d_code   
while @@fetch_status=0         
begin   
         print @t_garag_number   
         declare cur_AD cursor local read_only for   
          select long,lat, tele_time, analog_data, CurrentRun,valid   
          from bn.dbo.SYS_DEV_ArchiveData WITH(nolock,INDEX(IX2_SYS_DEV_ArchiveData))   
          where ObjectID=@t_id and tele_time between @start_time and @stop_time   
          --and long<>0 and lat<>0   
          order by tele_time;   

         set @pr_tele_time=null;   
         open cur_AD   
--    print 'цикл по коорд'   

         fetch next from cur_AD into @long, @lat, @tele_time, @analog_data, @CURRENT_MILAGE,@valid   
         while @@fetch_status=0 begin   
          if @pr_tele_time is not null begin   
           set @len_on_coord=bn.dbo.SYS_GetDistanceOnEarth (@long,@lat,@pr_long,@pr_lat)   
           set @len_on_mil=@CURRENT_MILAGE-@pr_CURRENT_MILAGE   
           if abs(@len_on_coord-@len_on_mil)>@dopusk         
           begin   
--    print '@long='+cast(@long as varchar(16))   
--    print '@lat='+cast(@lat as varchar(16))   
--    print '@tele_time='+cast(@tele_time as varchar(16))   
--    print 'abs(@len_on_coord-@len_on_mil)='+cast(abs(@len_on_coord-@len_on_mil) as varchar(16))   
            set @error_num=@error_num+1;   
            insert into  #temp   
            values (@error_num,   
            @t_id,
            @t_garag_number,   
            @t_gos_number,   
            @d_code,   
            @pr_long,   
            @pr_lat,   
            @pr_tele_time ,   
            @pr_analog_data,   
            @pr_CURRENT_MILAGE,   
            @pr_valid,   
            @len_on_coord, @len_on_mil   
            )   

            insert into  #temp   
            values (@error_num,   
            @t_id,
            @t_garag_number,   
            @t_gos_number,   
            @d_code,   
            @long,   
            @lat,   
            @tele_time,   
            @analog_data ,   
            @CURRENT_MILAGE,   
            @valid,   
            @len_on_coord, @len_on_mil   
            )   
                    
           end -- if abs(@len_on_coord-@len_on_mil)>@dopusk   
          end --if @pr_tele_time is not null begin   
          -- запоним пред значения данных, для сравнения   
          set @pr_long=@long   
          set @pr_lat=@lat   
          set @pr_tele_time=@tele_time   
          set @pr_analog_data=@analog_data   
          set @pr_CURRENT_MILAGE=@CURRENT_MILAGE   
          set @pr_valid=@valid   
          fetch next from cur_AD into @long, @lat, @tele_time, @analog_data, @CURRENT_MILAGE,@valid   
         end -- while @@fetch_status=0 begin fetch next from cur_AD into         
         close cur_AD   
         deallocate cur_AD   

         fetch next from cur_transport into @t_id, @t_garag_number, @t_gos_number, @d_code   

end -- while @@fetch_status=0 fetch next from cur_transport into   
close cur_transport   
deallocate cur_transport   

-- результат   
select * from #temp         
order by error_num   
--where len_on_mil<>0 -- закомментируйте фильтр, если хотите видеть все случаи   

-- табл "итогов"...   
select objectid,garag_num, count(error_num)/2 as errors,sum(len_on_mil)/2 as sum_len_mil from #temp   
group by garag_num,objectid   
order by garag_num;   

drop table #temp