Substract time-values from same column in SSRS

Question

I need to do substract time-values from the same column. I already orderd the values that need to be substracted from each other. I will add a picture of my table, so you have a general idea. Now I need to substract number 1 from number 2, number 2 from number 3,.... (from same vehicle) And put it in a column named Consumption for example.

As an If function gave me an error i tried to go to a case function. But now I get the error : "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. Problem code is the CASE-function till @lastvalue = AGVLoggingrunstate.starttime. I apparently can't put this code inside data-retrieval code. So how do I have to do it ? Do I need to make a stored procedure ? Tips are welcome.

Declare @lastSN AS nvarchar, @lastValue int;  
SET @lastSN = 'AGV';
SET @lastValue = 0;

SELECT ROW_NUMBER() OVER (ORDER BY AgvLoggingRunstate.vehicle,AgvLoggingRunstate.starttime) AS Row,
  AgvLoggingRunstate.id,
  AgvLoggingRunstate.vehicle,
  AgvLoggingRunstate.node,
  AgvLoggingRunstate.runstate,
  AgvLoggingRunstate.orderclass,
  AgvLoggingRunstate.loaded,
  AgvLoggingRunstate.starttime,
  AgvLoggingRunstate.endtime,
  DATEDIFF(second,AgvLoggingRunstate.starttime,AgvLoggingRunstate.endtime) AS DiffDate,
Case 
WHEN @lastSN = AgvLoggingRunstate.vehicle Then AgvLoggingRunstate.starttime - @lastValue 
ELSE 0 
END AS Consumption,

@lastSN = AgvLoggingRunstate.vehicle,
@lastValue = AgvLoggingRunstate.starttime


FROM  AgvLoggingRunstate

Where AgvLoggingRunstate.starttime > @Startdate and   AgvLoggingRunstate.starttime < @Enddate and AgvLoggingRunstate.runstate = 'Battery'

Order by

AgvLoggingRunstate.vehicle,
AgvLoggingRunstate.starttime

Result after Answer and adding an order by function :

Now the only incorrect answer is between two days. I need to filter this out. (Still reading all the info over COALESCE,LEFT JOIN,.. Because answer isn't completely clear yet to me)

WITH Times (RowNumber, vehicle, starttime,endtime)
AS
(SELECT ROW_NUMBER() OVER (ORDER BY vehicle, starttime),vehicle, starttime,endtime FROM AgvLoggingRunState WHERE starttime > @Startdate and  starttime < @Enddate and AgvLoggingRunstate.runstate = 'Battery') 

SELECT CurrentTime.Vehicle,
             CurrentTime.StartTime,
             CurrentTime.EndTime, 
             NextTime.StartTime AS NextTime,
             COALESCE(DATEDIFF(SECOND,CurrentTime.StartTime,NextTime.StartTime),0)                      
             AS Seconds,
             COALESCE(DATEDIFF(SECOND,CurrentTime.StartTime,CurrentTime.EndTime),0) 
             AS SecondsDiffEnd
FROM Times CurrentTime
  LEFT OUTER JOIN Times NextTime
  ON CurrentTime.RowNumber +1  = NextTime.RowNumber
  AND CurrentTime.Vehicle = NextTime.Vehicle  

Order by StartTime

Answer 1

You started correctly with the row number function. However, you're trying to do with a variable what you need to do with a join. In the example below, you'll see how I can create the CTE with your row number and then use that in the query, joining to the next record (as long as the vehicles match). You'll need to determine the correct answer for the last record per vehicle. Right now, it's zero (see the Coalesce Function) This would be really easy in a future version with LEAD. Oh well.

WITH Times (RowNumber, Vehicle, StartTime)
AS 
(SELECT ROW_NUMBER() OVER(ORDER BY Vehicle, StartTime),
 Vehicle, StartTime, NextTime FROM dbo.AvgLoggingRunState)

SELECT CurrentTime.Vehicle, 
       CurrentTime.StartTime, 
       NextTime.StartTime AS NextTime,
       COALESCE(DATEDIFF(SECOND,CurrentTime.StartTime,NextTime.StartTime),0)                     
          AS SecondsDiffNext,
      COALESCE(DATEDIFF(SECOND,CurrentTime.StartTime,CurrentTime.EndTime),0) 
          AS SecondsDiffEnd
FROM Times CurrentTime
   -- join to the next time - note that I join both on row number and
   -- on vehicles as I don't want to mix vehicles
  LEFT OUTER JOIN Times NextTime
  ON CurrentTime.RowNumber +1  = NextTime.RowNumber
  AND CurrentTime.Vehicle = NextTime.Vehicle