use bn_patp -- имя база данных
go
declare @mname varchar(64)

if (not object_id('tempdb..#temp0084356') IS NULL)
drop table #temp0084356;

      with points as (
      select mlist_id, point_id, ord,long,lat,[len] from dbo.PATP_MarshrutData as MD
      inner join dbo.PATP_Points as p on p.id=md.point_id)

      select --top 1000     
      m.name, md.id as mid,p.id as pid, md.mlist_id,md.ord,p.caption,
      dbo.PATP_GetDirection (pos.long,pos.lat,p.long,p.lat) as dir1,
      dbo.PATP_GetDirection (p.long,p.lat,pos2.long,pos2.lat) as dir2,
      p.len+pos2.len as lensum,
      dbo.PATP_GetLength2Points (pos.long,pos.lat,pos2.long,pos2.lat)as leng,     
      abs((p.len+pos2.len)-dbo.PATP_GetLength2Points (pos.long,pos.lat,pos2.long,pos2.lat)) as razn,
      p.long as long,p.lat as lat,
      pos.long as plong,pos.lat as plat,
      pos2.long as nlong,pos2.lat as nlat
      , case when (pos.lat<>pos2.lat and pos.long<>pos2.long) then [dbo].[PATP_GetLengthToLine] (p.long,p.lat,pos.lat,pos.long,pos2.lat,pos2.long)
       else -1 end  as lentopoint
       ,smd.id
      into #temp0084356
      from dbo.PATP_MarshrutData as MD
      inner join dbo.PATP_Points as p on p.id=md.point_id
      inner join points as pos on pos.mlist_id=md.mlist_id and pos.ord=md.ord-1
      inner join points as pos2 on pos2.mlist_id=md.mlist_id and pos2.ord=md.ord+1
      inner join dbo.PATP_MarshrutList as ml on ml.id=md.mlist_id
      inner join dbo.PATP_Marshruts as M on m.id=ml.Marshrut_id
      left join PATP_SubMarshrutData as SMD on SMD.mData_id=md.id
      where     

      (abs(dbo.PATP_GetDirection (pos.long,pos.lat,p.long,p.lat)-dbo.PATP_GetDirection (p.long,p.lat,pos2.long,pos2.lat))<=1 ) -- направления различаются на 1 или менее градусов
      and p.pointtype_id<>2 and p.iscontrolpoint<>1 and p.isend<>1-- не остановка, не контрольная, не конечная
      and abs((p.len+pos2.len)-dbo.PATP_GetLength2Points (pos.long,pos.lat,pos2.long,pos2.lat))<=1 -- разница расстояний отрезков ([1-2]+[2-3]) и [1-3] меньше 4 метров
      and     
      case when (pos.lat<>pos2.lat and pos.long<>pos2.long) then [dbo].[PATP_GetLengthToLine] (p.long,p.lat,pos.lat,pos.long,pos2.lat,pos2.long)
       else -1 end <=1 -- высота треуголника меньше 1 метров
      and (p.caption is null or rtrim(ltrim(p.caption))='') -- нет описания у точки
      and smd.id is null -- нет связи с подмаршрутом
      and m.active=1
       select  * from #temp0084356 order by name,ord;

       select m.name, m.active,m.mlist_id, m.c as 'point in m',t.c 'point to del', (0.0000+t.c)/m.c*100 as '%' from     
       (select m.name name,ML.id as mlist_id, count(*)as c,m.active as active from dbo.PATP_MarshrutData as MD
       inner join dbo.PATP_MarshrutList as ML on ML.id=MD.mList_id
       inner join dbo.PATP_Marshruts as M on m.id=ML.Marshrut_id
       group by m.name,m.active,ML.id
       ) as m
       left join (select  name,mlist_id, count(*) as c from #temp0084356 group by name,mlist_id) as t on t.mlist_id=m.mlist_id
       where m.active=1
       order by '%' desc

drop table #temp0084356