/****** Сценарий для команды SelectTopNRows среды SSMS  ******/
-- сценарий проверка работы датчика зажигания во время движения
-- выдает таблицу, где для каждого ТС указано общее количество фактов движения (скорость больше 5 км)
-- и сколько из них с датчиком зажагания.

set dateformat dmy
use BN
declare @sdate datetime
select @sdate='18/08/2012'

select org.ShortName, d.code,s.description,o.ObjectGosNum,o.ObjectGarNum, isnull(t.c,0) AllCoord,isnull(t2.c,0) [Ign],
case when t.c is null then 100 else isnull((0.0000+t2.c)/t.c*100,0) end [proc]
   FROM [BN].[dbo].[SYS_DEV_Sensor] as S
   inner join dbo.SYS_DEV_Device as D on d.id=s.device_id
   inner join dbo.FLEET_Object as O on o.Device_ID=d.id
  left join (select O.objectID objectID, count(*) c
  FROM [BN].[dbo].[SYS_DEV_Sensor] as S
  inner join dbo.SYS_DEV_Device as D on d.id=s.device_id
  inner join dbo.FLEET_Object as O on o.Device_ID=d.id
  inner join dbo.SYS_DEV_ArchiveData as AD on ad.ObjectID=O.ObjectID

  where [SensorClassificationId]=2 and d.deviceactive=1 and o.ObjectActive=1 and speed>5
  and ad.tele_time>=@sdate and s.SensorActive=1
  group by o.objectID) as T on t.objectID=O.ObjectID  
   left join dbo.SYS_OrgRequisite as Org on Org.id=O.OrganizationId
    
   left join (select O.objectID objectID, count(*) c
  FROM [BN].[dbo].[SYS_DEV_Sensor] as S
  inner join dbo.SYS_DEV_Device as D on d.id=s.device_id
  inner join dbo.FLEET_Object as O on o.Device_ID=d.id
  inner join dbo.SYS_DEV_ArchiveData as AD on ad.ObjectID=O.ObjectID

  where [SensorClassificationId]=2 and d.deviceactive=1 and o.ObjectActive=1 and dbo.FLEET_IsSensorActive (num, ad.Digit_Sens_Num, ad.Flags)=1  and speed>5
  and ad.tele_time>=@sdate and s.SensorActive=1
  group by o.objectID) as T2 on t2.objectID=O.ObjectID  
    
    where [SensorClassificationId]=2 and d.deviceactive=1 and o.ObjectActive=1 and s.SensorActive=1
    and case when t.c is null then 100 else isnull((0.0000+t2.c)/t.c*100,0) end<=95
     
    order by ShortName,o.ObjectGarNum