Code
use bn
set dateformat dmy
declare @start datetime, @stop datetime
select @start='10.08.2012',@stop='11.08.2012'
select org.ShortName,o.ObjectGarNum,c.tele_time,isnull(c.col,0) coun_all,isnull(c2.col,0) count,
case when c.col=0 then -100 else ((0.0000+c2.col)/c.col*100) end as perc
from dbo.FLEET_Object as o
left join (select ObjectID,convert(varchar(16),tele_time,102) as tele_time,COUNT(*) as col from dbo.SYS_DEV_ArchiveData
where tele_time between @start and @stop
group by ObjectID,convert(varchar(16),tele_time,102)
) as c on c.ObjectID=o.ObjectID
left join
(select ObjectID,convert(varchar(16),tele_time,102) as tele_time, COUNT(*) as col from dbo.SYS_DEV_ArchiveData
where tele_time between @start and @stop and flags&4=0
group by ObjectID,convert(varchar(16),tele_time,102)
) as c2 on c2.ObjectID=o.ObjectID and c.tele_time=c2.tele_time
left join dbo.SYS_OrgRequisite as Org on Org.id=O.OrganizationId
where isnull(c.col,0)>100 AND isnull(c2.col,0)>100
order by org.ShortName,o.ObjectGarNum,c.tele_time
на выходе таблица, где указаны колонки:
Фирма, гаражный номер, дата, общее кол-во координат, кол-во координат с ЧЯ, процент ЧЯ
