ALTER procedure  [dbo].[P_GetJSMapInfoData]
@GspDataTime nvarchar(20),
@GspDataTimeStart datetime,
@GspDataTimeEnd datetime,
@MaxLongitude nvarchar(50),
@MinLongitude nvarchar(50),
@MaxLatitude nvarchar(50),
@MinLatitude nvarchar(50)
as
 begin
 declare @FName1 varchar(20)
 declare @FName2 varchar(20)
 declare @FName3 varchar(20)
 declare @FName4 varchar(20)
 declare @FName5 varchar(20)
 set @FName1 = 'gps001'+@GspDataTime
 set @FName2 = 'gps002'+@GspDataTime
 set @FName3 = 'gps003'+@GspDataTime
 set @FName4 = 'gps004'+@GspDataTime
 set @FName5 = 'gps005'+@GspDataTime
 declare @S nvarchar(4000)
 set @S='select t1.VehicleLic,t1.Taotal,t2.Anomaly from
 (select count(a.Vehicleid) as Taotal,b.VehicleLic from '+@FName1+' as a,VehicleInfo as b
 where a.Vehicleid = b.Vehicleid and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t1 left join
 (select  count(a.Longitude) as Anomaly,b.VehicleLic from '+@FName1+' as a,VehicleInfo as b 
 where a.Vehicleid = b.Vehicleid and
 ((Longitude > '+@MaxLongitude+' or Longitude < '+@MinLongitude+' ) and(Latitude > '+@MaxLatitude+' or Latitude < '+@MinLatitude+' ))
 and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t2
 on t1.VehicleLic = t2.VehicleLic
 union
 select t1.VehicleLic,t1.Taotal,t2.Anomaly from
 (select count(a.Vehicleid) as Taotal,b.VehicleLic from '+@FName2+' as a,VehicleInfo as b
 where a.Vehicleid = b.Vehicleid and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t1 left join
 (select  count(a.Longitude) as Anomaly,b.VehicleLic from '+@FName2+' as a,VehicleInfo as b 
 where a.Vehicleid = b.Vehicleid and
 ((Longitude > '+@MaxLongitude+' or Longitude < '+@MinLongitude+' ) and(Latitude > '+@MaxLatitude+' or Latitude < '+@MinLatitude+' ))
 and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t2
 on t1.VehicleLic = t2.VehicleLic
 union
 select t1.VehicleLic,t1.Taotal,t2.Anomaly from
 (select count(a.Vehicleid) as Taotal,b.VehicleLic from '+@FName3+' as a,VehicleInfo as b
 where a.Vehicleid = b.Vehicleid and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t1 left join
 (select  count(a.Longitude) as Anomaly,b.VehicleLic from '+@FName3+' as a,VehicleInfo as b 
 where a.Vehicleid = b.Vehicleid and
 ((Longitude > '+@MaxLongitude+' or Longitude < '+@MinLongitude+' ) and(Latitude > '+@MaxLatitude+' or Latitude < '+@MinLatitude+' ))
 and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t2
 on t1.VehicleLic = t2.VehicleLic
 union
 select t1.VehicleLic,t1.Taotal,t2.Anomaly from
 (select count(a.Vehicleid) as Taotal,b.VehicleLic from '+@FName4+' as a,VehicleInfo as b
 where a.Vehicleid = b.Vehicleid and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t1 left join
 (select  count(a.Longitude) as Anomaly,b.VehicleLic from '+@FName4+' as a,VehicleInfo as b 
 where a.Vehicleid = b.Vehicleid and
 ((Longitude > '+@MaxLongitude+' or Longitude < '+@MinLongitude+' ) and(Latitude > '+@MaxLatitude+' or Latitude < '+@MinLatitude+' ))
 and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t2
 on t1.VehicleLic = t2.VehicleLic
 union
 select t1.VehicleLic,t1.Taotal,t2.Anomaly from
 (select count(a.Vehicleid) as Taotal,b.VehicleLic from '+@FName5+' as a,VehicleInfo as b
 where a.Vehicleid = b.Vehicleid and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t1 left join
 (select  count(a.Longitude) as Anomaly,b.VehicleLic from '+@FName5+' as a,VehicleInfo as b 
 where a.Vehicleid = b.Vehicleid and
 ((Longitude > '+@MaxLongitude+' or Longitude < '+@MinLongitude+' ) and(Latitude > '+@MaxLatitude+' or Latitude < '+@MinLatitude+' ))
 and gpsDateTime > '''+convert(varchar(60), @GspDataTimeStart,121)+'''
 and gpsDateTime < '''+convert(varchar(60),@GspDataTimeEnd,121)+''' group by b.VehicleLic)as t2
 on t1.VehicleLic = t2.VehicleLic'
 print(@S)
 exec(@S)
 end

go