title: Substract time-values from same column in SSRS
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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 &gt; @Startdate and   AgvLoggingRunstate.starttime &lt; @Enddate and AgvLoggingRunstate.runstate = 'Battery'
  
  Order by
  
  AgvLoggingRunstate.vehicle,
  AgvLoggingRunstate.starttime
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/JZAms.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JZAms.png" alt="table"></a></p>
  
  <p>Result after Answer and adding an <code>order by</code> function :</p>
  
  <p><a href="https://i.stack.imgur.com/7nDb3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7nDb3.png" alt="enter image description here"></a></p>
  
  <p>Now the only incorrect answer is between two days. I need to filter this out. (Still reading all the info over <code>COALESCE</code>,<code>LEFT JOIN</code>,.. Because answer isn't completely clear yet to me)</p>
  
  <pre><code>WITH Times (RowNumber, vehicle, starttime,endtime)
  AS
  (SELECT ROW_NUMBER() OVER (ORDER BY vehicle, starttime),vehicle, starttime,endtime FROM AgvLoggingRunState WHERE starttime &gt; @Startdate and  starttime &lt; @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
  </code></pre>