2006年9月8日,修改存储过程GetVehStation
修改原因:如果车辆不支持里程的话就不能查出来数据。
修改办法:把查询中的限制条件--空车里程不能为空给屏蔽掉。
修改后的存储过程
CREATE
Procedure
GetVehStation
@Minute
int
,
--
间隔时间
@StartTime
datetime
,
--
开始时间
@EndTime
datetime
,
--
结束时间
@VehNo
varchar
(
10
)
--
车牌号码
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*
根据车牌号码取出一定时间间隔内车辆的位置数据
*/
as
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set
nocount
on
Create
Table
#VehStatus
(
VehNo
varchar
(
10
),
Deviceid
varchar
(
10
),
sTime
datetime
,
Longitude
decimal
(
8
,
5
),
Latitude
decimal
(
7
,
5
),
Mile
int
)
begin
if
datalength
(
@VehNo
)
>
0
begin
insert
into
#VehStatus
select
Veh.V_NO,deviceid,Time,Longitude,Latitude,(WL_EmptyMile
-
Mile)
as
Mile
from
Dyndata
as
Dyn
left
join
vehicle
as
Veh
on
Dyn.Deviceid
=
Veh.V_Deviceid
left
join
(
select
Min
(WL_EmptyMile)
as
Mile ,V_No
from
Dyndata
left
join
Vehicle
on
Vehicle.V_Deviceid
=
Dyndata.Deviceid
where
Dyndata.Time
>=
@StartTime
and
V_No
=
@VehNo
group
by
V_No) A
on
A.V_No
=
Veh.V_No
where
Veh.V_No
=
@VehNo
and
datediff
(mi,
convert
(
datetime
,
@StartTime
,
121
),Time)
%
@Minute
=
0
and
time
<=
convert
(
datetime
,
@EndTime
,
121
)
and
Dyn.Locate
=
1
--
and Dyn.WL_EmptyMile is not null ///这里给屏蔽掉
end
select
*
from
#VehStatus
return
end
GO