use bn_patp -- база citybus
go
select  dt.device_code, count(dt.id) as count, max(dt.tele_time) as max, min(dt.tele_time) as min, t.Garag_Number,t.id,d.id as id_pribora from sys_dev_dirtydata as dt
left join SYS_DEV_Device as d on dt.device_code=d.code and d.Active=1
left join PATP_Transport as t on t.Device_id=d.id --and t.Active=1
--where datediff(mm,tele_time,getdate())<10
where t.id is null
group by dt.device_code, t.Garag_Number,t.id,d.id
order by count desc