Monday, December 31, 2012

Torque Stats with a CTE

Using a Common Table Expression in SQL to analyze some Performance stats from the OBD scanner.

;WITH cteRawData AS (
SELECT CAST(TripAvgMPG as float) TripAvgMPG,
ROW_NUMBER() OVER(PARTITION by runid order by CAST(DeviceTime as datetime)  DESC) as RowNum
from rawdata
AVG(CAST(r.SpeedOBD as float)) AvgSpeedOBD,
MAX(CAST(r.SpeedOBD as float)) MAXSpeedOBD,
MAX(CAST(r.DeviceTime as DateTime)) DeviceTime,
AVG(CAST(r.AbsThrottlePositionPCT as float)) AVGAbsThrottlePositionPCT,
AVG(CAST(r.EngineRPM as Float)) AvgEngineRPM,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeMoving as float)) - MIN(CAST(TripTimeMoving as float)),0),108) TripTimeMoving,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeOverall as float)) - MIN(CAST(TripTimeOverall as float)),0),108) TripTimeOverall,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeStationary as float)) - MIN(CAST(TripTimeStationary as float)),0),108) TripTimeStationary
FROM cteRawData c
JOIN rawdata r ON c.runid = r.runid
WHERE c.RowNum = 1
GROUP BY c.TripAvgMPG,c.RowNum,c.runid
order by c.TripAvgMPG