для среды MSDE
Code
/****** Сценарий для команды SelectTopNRows среды SSMS ******/
declare @t table (idsensor int, mintarval float)
declare @MinValue_Real float
,@MaxValue_Real float
,@MinValue_Tar float
,@MaxValue_Tar float
declare @lastMinValue_Real float
,@lastMaxValue_Real float
,@lastMinValue_Tar float
,@lastMaxValue_Tar float
declare cur cursor local for
select distinct idsensor from [BN].[dbo].[FLEET_AnalogTarirovka]
declare @idsensor int
open cur
fetch next from cur into @idsensor
while @@fetch_status=0 begin
declare cur2 cursor local FORWARD_ONLY for
select [MinValue_Real]
,[MaxValue_Real]
,[MinValue_Tar]
,[MaxValue_Tar] from [BN].[dbo].[FLEET_AnalogTarirovka]
where [IDSensor]=@idsensor
order by [MinValue_Real]
select @lastMinValue_Real=null,@lastMaxValue_Real=null,@lastMinValue_Tar=null,@lastMaxValue_Tar=null
open cur2
fetch next from cur2 into @MinValue_Real,@MaxValue_Real,@MinValue_Tar,@MaxValue_Tar
while @@fetch_status=0 begin
if @lastMinValue_Real is not null
begin
if @lastMaxValue_Real<>@MinValue_Real or @lastMaxValue_Tar<>@MinValue_Tar begin
insert into @t
values (@idsensor,@MinValue_Real)
print @idsensor
print @lastMaxValue_Real
print @MinValue_Real
print @lastMaxValue_Tar
print @MaxValue_Tar
end
end
select @lastMinValue_Real=@MinValue_Real,@lastMaxValue_Real=@MaxValue_Real,@lastMinValue_Tar=@MinValue_Tar,@lastMaxValue_Tar=@MaxValue_Tar
fetch next from cur2 into @MinValue_Real,@MaxValue_Real,@MinValue_Tar,@MaxValue_Tar
end
close cur2
deallocate cur2
fetch next from cur into @idsensor
end
close cur
deallocate cur
select s.sensoractive, mintarval as error_on_str_min_tar,
d.code,o.ObjectGarNum,o.ObjectGosNum from @t as t
inner join bn.dbo.SYS_DEV_Sensor as s on s.id=t.idsensor
inner join bn.dbo.SYS_DEV_Device as d on d.id=s.device_id
left join bn.dbo.FLEET_Object as O on O.Device_ID=d.id
where d.deviceactive=1
Проверяется, что в тарировночной таблице нет пропусков, нет пересечений данных
на выходе таблица с списком проблемных датчиков для проверки
