-------------------按车牌去更新----------------------------------
------------------连续时间的定义是:下一个时间(date)=上一个时间(date)+保持秒数(BCMS)
--如果是刹车状态:
--当前速度>上一速度+12km/hr,则当前速度=上一速度+12km/hr(加速);
--或者当前速度<上一速度-120km/hr,当前速度=上一速度(减速)。
--如果非刹车状态:
--当前速度>上一速度+45km/hr,当前速度=上一速度(加速);
--或者当前速度<上一速度-12km/hr,当前速度=上一速度-12km/hr(减速)。
ALTER PROCEDURE [dbo].[PROC_UpdateVdrdataBYVEHICLE]
AS
declare @BVehicleid varchar(16)--------上一条记录车牌
declare @EVehicleid varchar(16)--------下一条记录车牌
declare @onetime datetime ------------上一条记录的时间
declare @twotime datetime ------------下一条记录的时间
declare @onecarspeed int ------------上一条记录的车速
declare @twocarspeed int ------------下一条记录的车速
declare @onebcms float ------------上一条记录的保持秒数
declare @twobcms float ------------下一条记录的保持秒数
declare @lasttime datetime -----------中间值
declare @Count int ------------记录数
declare @SC int ------------刹车状态 0:刹车,1:非刹车
BEGIN-----------0-----------------
if object_id('tempdb..#CarTmp') is not null drop table #CarTmp --删除临时表
select IDENTITY(int,1,1) AS ID_Num,VehicleID,DriverID,Date,CarSpeed,CM,ZZD,YZD,YGD,AQD,XH2,SC,WZXH,BCMS into #CarTmp from TB_VDRDataTestBYVEHICLEID with(nolock) order by VehicleID,date asc
declare @id bigint
set @id=1
select @count=count(*) from #CarTmp
while @ID<=@Count
begin --------------1------------------
select @BVehicleid=VehicleID,@onetime=date,@onecarspeed=carspeed,@SC=sc,@onebcms=bcms from #CarTmp with(nolock) where ID_Num=@ID
select @EVehicleid=VehicleID,@twotime=date,@twocarspeed=carspeed,@SC=sc,@twobcms=bcms from #CarTmp with(nolock) where ID_Num=@ID+1
set @lasttime=dateadd(ss,@onebcms,@onetime)
if @BVehicleid=@EVehicleid
begin-----------------9----------------
if datediff(ss,@twotime,@lasttime)=0 ----------------如果两条记录为连续的
begin---------------2-------------------
if @sc=0 ---------------如果在刹车状态
begin-------------3------------------
if @twocarspeed>(@onecarspeed+12)
begin ---------4-------------------
set @twocarspeed=(@onecarspeed+12)
update #CarTmp set carspeed=@twocarspeed where ID_num=@ID+1
end ----------4-------------------
else if @twocarspeed<(@onecarspeed-120)
begin -----------5---------------
set @twocarspeed=@onecarspeed
update #CarTmp set carspeed=@twocarspeed where ID_num=@ID+1
end -------------5---------------
end --------------3------------------
if @SC=1-----------------如果非刹车状态
begin ---------------6--------------------
if @twocarspeed>(@onecarspeed+45)
begin ---------7-------------
set @twocarspeed=@onecarspeed
update #CarTmp set carspeed=@twocarspeed where ID_num=@ID+1
end ---------7-------------
else if @twocarspeed<(@onecarspeed-12)
begin -----------8-----------
set @twocarspeed=(@onecarspeed-12)
update #CarTmp set carspeed=@twocarspeed where ID_num=@ID+1
end -----------8-----------
end -----------------6--------------------
end-----------------2--------------------
set @ID=@ID+1
print @ID
end -----------------------9------------------------
end----------------1------------------
truncate table TB_VDRDataTestBYVEHICLEID
insert into TB_VDRDataTestBYVEHICLEID select VehicleID,DriverID,Date,CarSpeed,CM,ZZD,YZD,YGD,AQD,XH2,SC,WZXH,BCMS from #CarTmp
if object_id('tempdb..#CarTmp') is not null drop table #CarTmp --删除临时表
END-------------------0--------------------
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货