定義一個Nums表
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(numid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,numsdate smalldatetime NOT NULL,);
go
DECLARE @i AS INT = 1;
BEGIN TRAN
WHILE @i <= 100000
BEGIN
INSERT INTO dbo.Nums VALUES(DATEADD(day, @i - 1, '20090101'));
SET @i = @i + 1;
END
COMMIT TRAN
SET NOCOUNT OFF;
GO
IF OBJECT_ID('dbo.sp_driving', 'P') IS NOT NULL
DROP PROC dbo.sp_driving;
GO
create procedure sp_driving
@uid as int=10000,
@fromdate AS DATETIME = '20130201',
@todate AS DATETIME = '20130331'
as
set nocount on;
select REC_ID= CASE WHEN ur.REC_ID is null THEN 0 ELSE ur.REC_ID END ,ACCELERATION=CASE WHEN ur.ACCELERATION is null THEN 0 ELSE ur.ACCELERATION END ,BRAKES=CASE WHEN ur.BRAKES is null THEN 0 ELSE ur.BRAKES END ,CHARIOTEST=CASE WHEN ur.CHARIOTEST is null THEN '' ELSE ur.CHARIOTEST END ,DRIVETIME=CASE WHEN ur.DRIVETIME is null THEN 0 ELSE ur.DRIVETIME END ,HIGHESTSPEED=CASE WHEN ur.HIGHESTSPEED is null THEN 0 ELSE ur.HIGHESTSPEED END ,MILE=CASE WHEN ur.MILE is null THEN 0 ELSE ur.MILE END ,OBDFUEL=CASE WHEN ur.OBDFUEL is null THEN 0 ELSE ur.OBDFUEL END ,OBDMILE=CASE WHEN ur.OBDMILE is null THEN 0 ELSE ur.OBDMILE END ,SPEED20T40=CASE WHEN ur.SPEED20T40 is null THEN 0 ELSE ur.SPEED20T40 END ,SPEED40T60=CASE WHEN ur.SPEED40T60 is null THEN 0 ELSE ur.SPEED40T60 END ,SPEED60T80= CASE WHEN ur.SPEED60T80 is null THEN 0 ELSE ur.SPEED60T80 END ,SPEEDL20=CASE WHEN ur.SPEEDL20 is null THEN 0 ELSE ur.SPEEDL20 END ,SPEEDU80=CASE WHEN ur.SPEEDU80 is null THEN 0 ELSE ur.SPEEDU80 END ,
n.numsdate as REC_DATE from dbo.nums as n LEFT JOIN (select * from (select * from USEROBDREC ure where ure.CHARIOTEST=(select uo.CHARIOTEST from UserOBD uo where [email protected] )) as usre where usre.REC_DATE>[email protected] and usre.REC_DATE<[email protected]) as ur on n.numsdate=ur.REC_DATE where n.numsdate>[email protected] and n.numsdate<[email protected] ;
go
EXEC dbo.sp_driving
@uid = 10019,
@fromdate = '20130201',
@todate = '20130301';
