declare @ShortName varchar(50),@group_name varchar(50), @Last_vav_hh int, @no_dut_hh int

select @ShortName='ДЭП 52', -- название фирмы (краткое)
@group_name ='Группа'
@Last_vav_hh=3, -- кол-во часов отсутствия навигации не более
@no_dut_hh=1 -- кол-во часов отсутствия данных от ДУТ не более

SELECT    
      OrgReq.ShortName
      ,
         [ObjectGarNum],
        [ObjectGosNum],d.code
        ,cd.tele_time as last_nav_time
        ,Obj_last_ign.num 'Ignition_num'
        ,Obj_last_ign.last_ignit
        ,isnull(count_dut.count_dut,0) as 'кол-во ДУТ'
        ,case when dut10.tele_time is null then 'нет' else ' '+convert(varchar(30),dut10.tele_time,120) end dut10_time
        ,isnull(round(dut10.[SensValue],0),'') dut10_value
        ,round(dut10.TarValue,2) dut10_tarvalue
        ,case when dut11.tele_time is null then 'нет' else ' '+convert(varchar(30),dut11.tele_time,120) end dut11_time
        ,isnull(round(dut11.[SensValue],0),'') dut11_value
        ,round(dut11.TarValue,2) dut11_tarvalue
        ,case when dut18.tele_time is null then 'нет' else ' '+convert(varchar(30),dut18.tele_time,120) end dut18_time
        ,isnull(round(dut18.[SensValue],0),'') dut18_value
        ,round(dut18.TarValue,2) dut18_tarvalue         
                
     FROM [BN].[dbo].[FLEET_Object] as O
     inner join [BN].[dbo].[SYS_DEV_Device] as D on d.id=O.Device_ID
     inner join bn.[dbo].[FLEET_ObjectRelation] as ORel on Orel.ObjectID=O.ObjectID
     inner join bn.[dbo].[FLEET_ObjectGroupe] as OG on Og.ObjectGroupeID=Orel.ObjectGroupeID
     left join bn.[dbo].[SYS_OrgRequisite] as OrgReq on OrgReq.id=O.OrganizationId
     left join [BN].[dbo].[SYS_DEV_CurrentData] as CD on cd.ObjectID=O.ObjectID
     left join (select device_id,count(*) count_dut from bn.[dbo].[SYS_DEV_Sensor]    
         where [sensor_type_id]=0 and [SensorActive]=1    
         and [SensorClassificationId]=1    
         group by device_id
         ) as Count_dut on Count_dut.device_id=o.Device_ID
     left join -- дут10
      (select device_id,tele_time,[SensValue],
         bn.dbo.SYS_GetTareValue (ID,[SensValue]) TarValue
         from bn.[dbo].[SYS_DEV_Sensor]    
         where [sensor_type_id]=0 and [num]=10 and [SensorActive]=1    
         and [SensorClassificationId]=1)    
         as dut10 on dut10.device_id=O.Device_ID
     left join -- дут11
      (select device_id,tele_time,[SensValue],
         bn.dbo.SYS_GetTareValue (ID,[SensValue]) TarValue
         from bn.[dbo].[SYS_DEV_Sensor]    
         where [sensor_type_id]=0 and [num]=11 and [SensorActive]=1    
         and [SensorClassificationId]=1)    
         as dut11 on dut11.device_id=O.Device_ID

left join -- дут18
      (select device_id,tele_time,[SensValue],
         bn.dbo.SYS_GetTareValue (ID,[SensValue]) TarValue
         from bn.[dbo].[SYS_DEV_Sensor]    
         where [sensor_type_id]=0 and [num]=18 and [SensorActive]=1    
         and [SensorClassificationId]=1)    
         as dut18 on dut18.device_id=O.Device_ID

left join -- последний факт зажигания
(select o.ObjectID,s.num num,max(ad.tele_time) as last_ignit from bn.[dbo].[SYS_DEV_ArchiveData] as AD
inner join bn.[dbo].[FLEET_Object] as O on o.Objectid=AD.ObjectID
inner join bn.[dbo].[SYS_DEV_Sensor] as S on s.device_id=ad.device_id and s.sensor_type_id=1 and s.SensorActive=1 and s.SensorClassificationId=2 and  digit_sens_num&power(2,s.num-1)>0

group by o.ObjectID,s.num) as Obj_last_ign on Obj_last_ign.ObjectID=O.ObjectID

     where o.ObjectActive=1
     --and OrgReq.ShortName = @ShortName -- фильтр по названию фирмы
     and og.ObjectGroupeName = @group_name
     and (
      datediff(HOUR,cd.tele_time,getdate())>@Last_vav_hh -- нет навигации дольше чем часов
      or (dut10.tele_time is not null and datediff(HOUR,dut10.tele_time,cd.tele_time)>=@no_dut_hh) -- данные от ДУТ10 отстают
      or (dut11.tele_time is not null and datediff(HOUR,dut11.tele_time,cd.tele_time)>=@no_dut_hh) -- данные от ДУТ11 отстают
      or (dut18.tele_time is not null and datediff(HOUR,dut18.tele_time,cd.tele_time)>=@no_dut_hh) -- данные от ДУТ18 отстают
      )

     order by OrgReq.ShortName,
[ObjectGarNum]

set nocount on  
declare @ShortName varchar(50),@group_name varchar(50), @Last_vav_hh int, @no_dut_hh int  
select @ShortName='ДЭП 52' -- название фирмы (краткое)  
,@Last_vav_hh=3 -- кол-во часов отсутствия навигации не более  
,@no_dut_hh=1 -- кол-во часов отсутствия данных от ДУТ не более  
SELECT  
    OrgReq.ShortName Организация  
    , [ObjectGarNum]Гаражный  
       , [ObjectGosNum]ГосНомер  
      ,d.code НомерАТ  
      ,[BN].[dbo].[Fleet_GetGroupNamesForObject] (o.ObjectID) Группы  
      ,cd.tele_time as ПослеНавигация  
      ,Obj_last_ign.num 'Номер дат Зажигания'  
      ,Obj_last_ign.last_ignit 'Последнее зажигание'  
      ,isnull(count_dut.count_dut,0) as 'кол-во ДУТ'  
      ,case when dut10.tele_time is null then 'нет' else ' '+convert(varchar(30),dut10.tele_time,120) end ДУТ10_Время  
      ,isnull(round(dut10.[SensValue],0),'') 'ДУТ10_значение'  
      ,round(dut10.TarValue,2) ДУТ10_литры  
      ,case when dut11.tele_time is null then 'нет' else ' '+convert(varchar(30),dut11.tele_time,120) end ДУТ11_Время  
      ,isnull(round(dut11.[SensValue],0),'') ДУТ11_Значение  
      ,round(dut11.TarValue,2) ДУТ11_Литры  
      ,case when dut18.tele_time is null then 'нет' else ' '+convert(varchar(30),dut18.tele_time,120) end ДУТ18_Время  
      ,isnull(round(dut18.[SensValue],0),'') ДУТ18_Значение  
      ,round(dut18.TarValue,2) ДУТ18_Литры  
   FROM [BN].[dbo].[FLEET_Object] as O   
   inner join [BN].[dbo].[SYS_DEV_Device] as D on d.id=O.Device_ID  
   left join bn.[dbo].[SYS_OrgRequisite] as OrgReq on OrgReq.id=O.OrganizationId  
   left join [BN].[dbo].[SYS_DEV_CurrentData] as CD on cd.ObjectID=O.ObjectID  
   left join (select device_id,count(*) count_dut from bn.[dbo].[SYS_DEV_Sensor] where [sensor_type_id]=0 and [SensorActive]=1 and [SensorClassificationId]=1  
       group by device_id ) as Count_dut on Count_dut.device_id=o.Device_ID  
   left join -- дут10  
    (select device_id,tele_time,[SensValue], bn.dbo.SYS_GetTareValue (ID,[SensValue]) TarValue from bn.[dbo].[SYS_DEV_Sensor] where [sensor_type_id]=0 and [num]=10 and [SensorActive]=1 And [SensorClassificationId]=1) as dut10 on dut10.device_id=O.Device_ID  
   left join -- дут11  
    (select device_id,tele_time,[SensValue],bn.dbo.SYS_GetTareValue (ID,[SensValue]) TarValue from bn.[dbo].[SYS_DEV_Sensor] where [sensor_type_id]=0 and [num]=11 and [SensorActive]=1 and [SensorClassificationId]=1) as dut11 on dut11.device_id=O.Device_ID  
left join -- дут18  
    (select device_id,tele_time,[SensValue], bn.dbo.SYS_GetTareValue (ID,[SensValue]) TarValue from bn.[dbo].[SYS_DEV_Sensor] where [sensor_type_id]=0 and [num]=18 and [SensorActive]=1 and [SensorClassificationId]=1) as dut18 on dut18.device_id=O.Device_ID  
left join -- последний факт зажигания  
(select o.ObjectID,s.num num,max(ad.tele_time) as last_ignit from bn.[dbo].[SYS_DEV_ArchiveData] as AD
inner join bn.[dbo].[FLEET_Object] as O on o.Objectid=AD.ObjectID  
inner join bn.[dbo].[SYS_DEV_Sensor] as S on s.device_id=ad.device_id and s.sensor_type_id=1 and s.SensorActive=1 and s.SensorClassificationId=2  
where ad.tele_time>=dateadd(dd,-30,getdate()) and digit_sens_num&power(2,s.num-1)>0  
group by o.ObjectID,s.num) as Obj_last_ign on Obj_last_ign.ObjectID=O.ObjectID where o.ObjectActive=1  order by OrgReq.ShortName,
[ObjectGarNum]