武汉逻辑判断

--燃油类型设置为空

update  RemoteDetection_ceshi set fuelType=null

 

--匹配车辆表数据

select r.REMOTEDETECTIONID,v.CARMODE,v.IUVTYPE,v.VRDATE,v.FactoryPlateModel,v.FUELTYPE,v.VehicleID

 into #table1 from RemoteDetection_ceshi r

 inner join Vehicle v on r.VLPN=v.VLPN and r.VLPNCOLOR=v.VLPNColor

 and r.FUELTYPE is null 

 order by PASSDATETIME desc

 

--修改遥测数据  

update RemoteDetection_ceshi set FuelType=v.FuelType,

IUVTYPE=v.IUVTYPE,VRDATE=v.VRDATE,FactoryPlateModel=v.FactoryPlateModel,VehicleID=v.VehicleID 

from #table1 v where RemoteDetection_ceshi.REMOTEDETECTIONID=v.REMOTEDETECTIONID

 

drop table #table1

 

--修改结果

update  RemoteDetection_ceshi set DETECTIONSTATE='1',Highemissions='0',IsValid='1'

 

--判断无效数据

update RemoteDetection_ceshi set DETECTIONSTATE=3,Highemissions=3,IsValid=0

from RemoteDetection_ceshi v

where REMOTEDETECTIONID=v.REMOTEDETECTIONID and WINDSPEED>5 or Humidity>85

or (Temperature<-20 or Temperature>45)

or (FUELTYPe='A' and (VSP<0 or VSP>20))

or ACCELERATION<0 or SPEED<=0

or NO>5000

or SF>100

or CO2>16

 

 

--不判定数据

update RemoteDetection_ceshi set DETECTIONSTATE='2',Highemissions='2' 

from RemoteDetection_ceshi v

where REMOTEDETECTIONID=v.REMOTEDETECTIONID and IsValid='1' 

and (VLPNCOLOR='05' or (ISNULL(SF,0)<=30 and FUELTYPE is Null))

 

--判断超限

update RemoteDetection_ceshi set DETECTIONSTATE=0

from RemoteDetection_ceshi v

where REMOTEDETECTIONID=v.REMOTEDETECTIONID

and IsValid='1' and VLPNCOLOR !='05'

and ( 

 (ISNULL(RgB,0)>1 and FUELTYPE='B')

 or (ISNULL(SF,0)>30 and FUELTYPE='B')

 or (ISNULL(CO,0)>1.6 and FUELTYPE='A')

 or (ISNULL(NO,0)>900 and FUELTYPE='A')

 or (ISNULL(SF,0)>30 and FUELTYPE is Null)

 )

 

 

 

--判断高排

update RemoteDetection_ceshi set Highemissions=1

from RemoteDetection_ceshi v

where REMOTEDETECTIONID=v.REMOTEDETECTIONID

and IsValid='1' and VLPNCOLOR !='05'

and ( 

  NO>1500 and FUELTYPE='B'

 )

posted @ 2020-01-08 16:40  不再犯错  阅读(...)  评论(...编辑  收藏