ALTER PROCEDURE [dbo].[spMileageStatement](
    @CarNo varchar(30),
    @StartTime datetime,
    @EndTime datetime,
    @Result varchar(10) out
    )
AS
DECLARE @Mileage VARCHAR(20)--里程
DECLARE @SQL VARCHAR(100)
DECLARE @GpsStartDay VARCHAR(8)--开始时间
DECLARE @GpsEndDay VARCHAR(8)--结束时间
DECLARE @GpsTime varchar(8)
DECLARE @WHERES VARCHAR(1000)
DECLARE @TEMP1 VARCHAR(1000)
DECLARE @TEMP2 VARCHAR(1000)
SET @GpsStartDay = CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@StartTime AS FLOAT)) AS DATETIME), 112)
SET @GpsEndDay=CONVERT( VARCHAR(8), CAST(FLOOR(CAST(@EndTime AS FLOAT)) AS DATETIME), 112)
SET @WHERES=' AND 1=1'

IF(@CarNo<>'' or @CarNo is not null)--查全部或者根据车牌号查
BEGIN
    SET @WHERES=' AND Car.CarNo='+@CarNo
END
--结束时间的里程
    SET @TEMP1='SELECT  top 1 Mileage
    FROM  dbo.Car 
    INNER JOIN dbo.Terminal ON dbo.Car.CarId = dbo.Terminal.CarId 
    INNER JOIN dbo.GpsPath'+@GpsTime+' ON dbo.Terminal.TerminalNo = dbo.GpsPath'+@GpsTime+'.TerminalNo
    WHERE GpsTime<='+@EndTime+@WHERES+'
    ORDER BY GpsTime desc '

    --开始时间的里程
    SET @TEMP2='SELECT  top 1 Mileage
    FROM  dbo.Car 
    INNER JOIN dbo.Terminal ON dbo.Car.CarId = dbo.Terminal.CarId 
    INNER JOIN dbo.GpsPath'+@GpsTime+' ON dbo.Terminal.TerminalNo = dbo.GpsPath'+@GpsTime+'.TerminalNo
    WHERE GpsTime>='+@StartTime+@WHERES+'
    ORDER BY GpsTime asc '

SET @Mileage=@TEMP1-@TEMP2
EXEC(@Mileage)
IF(@Mileage>0)--如果大于0，说明数据正常，如果小于0，说明基础数据不正常。
BEGIN
    SET @SQL='SELECT  MIN(UploadTime)AS 开始时间,MAX(UploadTime)AS 结束时间,CompanyName,Car.CarNo,'+@Mileage+'
FROM  dbo.Car 
INNER JOIN dbo.Terminal ON dbo.Car.CarId = dbo.Terminal.CarId 
INNER JOIN dbo.GpsPath'+@GpsTime+' ON dbo.Terminal.TerminalNo = dbo.GpsPath'+@GpsTime+'.TerminalNo
INNER JOIN dbo.Company ON dbo.Car.CompanyId=dbo.Company.CompanyId
group by CompanyName,Car.CarNo'
END
ELSE
BEGIN
    SET @Result='0'
    RETURN 
END

if(@GpsStartDay=@GpsEndDay)--开始时间与结束时间为同一天
BEGIN
    Set @GpsTime=@GpsStartDay--数据表为GpsPath加上这一天，例：GpsPath20120629
    exec(@SQL)
END