/****** Скрипт для команды SelectTopNRows из среды SSMS  ******/
SELECT TOP 1000
[device_id]
,[device_code]
,[tele_time]
,
[flags],case
when flags&1=0 -- стоянка
then
case when (getdate()-cd.tele_time)>(cast('1900-01-01 00:00:00.000' as datetime)+cast(d.opt_int2 as float)/86400)
then 'потеря связи' else '' end
else
case when getdate()-cd.tele_time>cast('1900-01-01 00:00:00.000' as datetime)+cast(d.opt_int1 as float)/86400 then 'потеря связи' else '' end
end
,case
when flags&1=0 -- стоянка
then 'стоянка'
else ''
end
FROM [BN].[dbo].[SYS_DEV_CurrentData] as CD
inner join [BN].[dbo].[SYS_DEV_Device] as D on d.id=CD.device_id

SELECT
[IDObject]FROM [BN].[dbo].[FLEET_Events]
where [WorkOffDateTime]is null
group by
[IDObject]

create table #t(sost int, ObjectID int)

insert into #t
select case    
when Ev. [IDObject]is not null then 5
when valid=0 then 4
when case     
         when flags&1=0 -- стоянка     
         then     
         case when (getdate()-cd.tele_time)>(cast('1900-01-01 00:00:00.000' as datetime)+cast(d.opt_int2 as float)/86400)    
         then 'потеря связи' else '' end     
         else     
         case when getdate()-cd.tele_time>cast('1900-01-01 00:00:00.000' as datetime)+cast(d.opt_int1 as float)/86400 then 'потеря связи' else '' end    
         end ='потеря связи' then 3
when Flags&4=0 then 2
when Flags&1=0 then 1
else 0
end
,cd.ObjectID

    from bn.dbo.SYS_DEV_CurrentData as CD
inner join bn.[dbo].FLEET_Object as o on o.ObjectID=cd.ObjectID
inner join bn.[dbo].[SYS_DEV_Device] as D on d.id=cd.device_id
left join (SELECT    
    [IDObject]FROM [BN].[dbo].[FLEET_Events]    
     where [WorkOffDateTime]is null    
     group by    
    [IDObject]) as Ev on Ev.[IDObject]=Cd.ObjectID

where o.ObjectActive=1

--select * from #t
select sost,count(*) from #t
group by sost
drop table #t