USE [BN]  
set nocount on  
GO  
declare   @PeriodFrom smalldatetime,  
      @PeriodTo smalldatetime,@GroupID int  
          

select @PeriodFrom='01-11-2011 00:00',@PeriodTo='16-11-2011 00:00'  
set @GroupID=null -- по всем группам
--set @GroupID=104 -- по конкретной группе
      Create table #TempData  
       (ObjectID int,              -- ID машины  
        CarGarNum varchar(256),    -- гаражный номер машины  
        ObjectGosNum varchar(20),  
        sensor_on datetime, -- начало работы сенсора  
        sensor_off datetime, -- завершение работы сенсора  
        num int)           -- его номер  

      Declare  
         @CarID int,     
         @CarGarNum varchar(256),  
         @ObjectGosNum varchar(20),  
         @TeleTime datetime,  
         @Digit_Sens_Num int,  
         @Flags int,  
         @IsActive bit,  
         @num int,  
         @time_sensor_on datetime,  
         @time_sensor_off datetime  
          
      if @GroupID is not null  
        Declare CarCursor cursor local fast_forward for  
        select a.ObjectID, a.ObjectGarNum, a.ObjectGosNum, c.MarkCarName  from FLEET_Object a  
        left join FLEET_ObjectRelation b on a.ObjectID = b.ObjectID  
        left join FLEET_MarkCar c on a.MarkCarID = c.MarkCarID  
        where b.ObjectGroupeID = @GroupID and a.ObjectActive = 1  
        else  
        Declare CarCursor cursor local fast_forward for  
        select a.ObjectID, a.ObjectGarNum, a.ObjectGosNum from FLEET_Object a  
        left join FLEET_MarkCar c on a.MarkCarID = c.MarkCarID  
        where ObjectActive = 1  
      Open CarCursor  
        while 0=0  
          begin  
            Fetch from CarCursor into @CarID, @CarGarNum, @ObjectGosNum  
            if @@Fetch_Status!=0 break  
            Set @IsActive = 0  

            Declare CoordCurs cursor local fast_forward for     
            select c.tele_time, c.Digit_Sens_Num, c.Flags, d.num     
            from SYS_DEV_ArchiveData c with(index(IX2_SYS_DEV_ArchiveData))  
            left join SYS_DEV_Sensor d on c.device_id = d.device_id  
            where     
              d.SensorClassificationId = 11 -- фильтр по классу датчика "Подъем кузова"
              and c.ObjectID = @CarID  
              and c.tele_time between @PeriodFrom and @PeriodTo  
            order by c.tele_time  
            select @time_sensor_on =null, @time_sensor_off =null  
            Set @IsActive = 0  
            Open CoordCurs  
              while 0=0  
                begin  
                  Fetch from CoordCurs into @TeleTime, @Digit_Sens_Num, @Flags, @num  
                  if @@Fetch_Status!=0 break  
                  if dbo.FLEET_IsSensorActive(@num, @Digit_Sens_Num, @Flags) = 1 -- датчик активен  
                    begin  
                       if @IsActive = 0 -- датчик был не активен  
                            begin       
                    Set @IsActive = 1  
                    select @time_sensor_on=@TeleTime,@time_sensor_off =null  
                            end  
                    end else  -- датчик не активен  
                    if @IsActive = 1  
                      begin  
         insert into #TempData (ObjectID,CarGarNum,ObjectGosNum,sensor_on , sensor_off,num)  
         values  (@CarID,@CarGarNum, @ObjectGosNum, @time_sensor_on,@TeleTime,@num)  
                        Set @IsActive = 0        
                        select @time_sensor_on=null      
                      end        
                end       
            Close CoordCurs  
            Deallocate CoordCurs       
          end  
      Close CarCursor  
      Deallocate CarCursor       
           
      select *, DATEDIFF (SS,sensor_on,sensor_off)as sec from #TempData     
      --where  DATEDIFF (SS,sensor_on,sensor_off)<=5  
      order by CarGarNum