一个存储过程
车辆油耗报表存储过程
1 Go
2 --exec tms_procTruckOilRate @beginDate='2009-4-22',@endDate='2009-4-23',@where='1=1 and Tms_viewDispatchOrder.ArriveTime >= ''2009-04-22'' and
3 --Tms_viewDispatchOrder.ArriveTime < ''2009-04-23'''
4 --车辆油耗报表
5 /************************************
6 创建 GoldKevin
7 车辆油耗报表存储过程
8 *************************************/
9 alter procedure tms_procTruckOilRate
10 @beginDate varchar(40),--开始时间
11 @endDate varchar(40),--截止时间
12 @where varchar(1000)--where 语句
13 as
14 declare @sql varchar(8000)
15 begin
16 set @sql='select MaskNumber,Sum(ISNULL(OilRate,0)) as ActSUMOilRate,
17 SUM(ISNULL(OilRate*(LeaveMileage+ReturnMileage+AdjustMileage+StartMileage+EndMileage)/100,0)) as SUMEDOilRate into #tempOilTable
18 From Tms_viewDispatchOrder Where ArriveTime>='''+@beginDate+''' and ArriveTime<'''+@endDate+'''
19 group by MaskNumber
20 select Tms_viewDispatchOrder.ArriveTime,Tms_viewDispatchOrder.MaskNumber,DriverName,TransportLineDescribe,
21 SizeType,ContainerNo,ISNULL(LeaveMileage+ReturnMileage+AdjustMileage+StartMileage+EndMileage,0) as Mileage,
22 ISNULL(Tms_viewDispatchOrder.OilRate*(LeaveMileage+ReturnMileage+AdjustMileage+StartMileage+EndMileage)/100,0) as EDOilRate,
23 #tempOilTable.ActSUMOilRate, #tempOilTable.ActSUMOilRate - #tempOilTable.SUMEDOilRate as SubOilRate,
24 '''+@beginDate+''' as BeignDate,convert(varchar(10),dateadd(day,-1,'''+@endDate+'''),120) as EndDate
25 from Tms_viewDispatchOrder LEFT OUTER JOIN #tempOilTable ON Tms_viewDispatchOrder.MaskNumber = #tempOilTable.MaskNumber
26 where '+@where +' Order by Tms_viewDispatchOrder.MaskNumber,Tms_viewDispatchOrder.ArriveTime Drop table #tempOilTable'
27 print @sql
28 exec(@sql)
29 end
1 Go
2 --exec tms_procTruckOilRate @beginDate='2009-4-22',@endDate='2009-4-23',@where='1=1 and Tms_viewDispatchOrder.ArriveTime >= ''2009-04-22'' and
3 --Tms_viewDispatchOrder.ArriveTime < ''2009-04-23'''
4 --车辆油耗报表
5 /************************************
6 创建 GoldKevin
7 车辆油耗报表存储过程
8 *************************************/
9 alter procedure tms_procTruckOilRate
10 @beginDate varchar(40),--开始时间
11 @endDate varchar(40),--截止时间
12 @where varchar(1000)--where 语句
13 as
14 declare @sql varchar(8000)
15 begin
16 set @sql='select MaskNumber,Sum(ISNULL(OilRate,0)) as ActSUMOilRate,
17 SUM(ISNULL(OilRate*(LeaveMileage+ReturnMileage+AdjustMileage+StartMileage+EndMileage)/100,0)) as SUMEDOilRate into #tempOilTable
18 From Tms_viewDispatchOrder Where ArriveTime>='''+@beginDate+''' and ArriveTime<'''+@endDate+'''
19 group by MaskNumber
20 select Tms_viewDispatchOrder.ArriveTime,Tms_viewDispatchOrder.MaskNumber,DriverName,TransportLineDescribe,
21 SizeType,ContainerNo,ISNULL(LeaveMileage+ReturnMileage+AdjustMileage+StartMileage+EndMileage,0) as Mileage,
22 ISNULL(Tms_viewDispatchOrder.OilRate*(LeaveMileage+ReturnMileage+AdjustMileage+StartMileage+EndMileage)/100,0) as EDOilRate,
23 #tempOilTable.ActSUMOilRate, #tempOilTable.ActSUMOilRate - #tempOilTable.SUMEDOilRate as SubOilRate,
24 '''+@beginDate+''' as BeignDate,convert(varchar(10),dateadd(day,-1,'''+@endDate+'''),120) as EndDate
25 from Tms_viewDispatchOrder LEFT OUTER JOIN #tempOilTable ON Tms_viewDispatchOrder.MaskNumber = #tempOilTable.MaskNumber
26 where '+@where +' Order by Tms_viewDispatchOrder.MaskNumber,Tms_viewDispatchOrder.ArriveTime Drop table #tempOilTable'
27 print @sql
28 exec(@sql)
29 end