You don't say what DB you are using, but that syntax looks like MS SQL Server.
Also you do not properly state the table structure - the function refers to VehicleID which doesn't appear in you sample data and you don't say what the data types or what indexes and keys are present. All of this could be significant.
You are pulling a bunch if data into a table variable and processing it there which could be a problem - if there are many rows per vehicle this will hit disk and cause slowness due to that IO.
With SQL Server 2012 or later this is quite easy to do using window functions:
SELECT ID, DATEDIFF(MINUTE
, TrackTime
, LEAD(TimeTrack) OVER (ORDER BY TimeTrack)
)
FROM Tracking
WHERE VehicleID = @VehicleID
AND TrackTime BETWEEN @starttime AND @endtime
AND Speed > @speedlimit
ORDER BY TimeTrack
For this to be efficient you'll at very least need an index covering VehicleID. Better still either one covering VehicleID & TrackTime (perhaps including speed & ID), or have VehicleID & TrackTime be your clustered index (though for a definite recommendation we need to know a lot more about the other queries the table sees: picking your clustered index to optimise this query might show others).
I'd your indexes are right this should pass over the data once, with and index seek rather than a scan (or worse, a table scan) and no spooling to disk unless the data per vehicle is really massive.
If you want results for multiple vehicles then you need to tell the lead function to split by VehicleID so it becomes LEAD(TimeTrack) OVER (PARTITION BY VehicleID ORDER BY TimeTrack)
, and add VehicleID to the main ordering clause too.
If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit:
CASE
WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1
WHEN timeToMaturity LIKE '%Weeks' THEN cast(rtrim(InterestRate.timeToMat,'abcdefghijklmnopqrstuvwxyz')as INT) * 7
WHEN timeToMaturity LIKE '%Months' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 30
WHEN timeToMaturity LIKE '%Years' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 365
ELSE 0
END AS TotalDaysToMaturity
Now, I realize that you may need to do something different when it comes to months as all months are not equal, but it may fit your needs. Perhaps someone would come by to figure that out dynamically.
Best Answer
SQL Fiddle
Use SUBSTRING_INDEX to isolate the minutes and seconds,convert minutes to seconds and add them up so the where condition has something to compare.Assuming all durations in the table have minutes and seconds separated by ':'