How to get difference between two rows for a column field?

by 
SELECT
   [current].rowInt,
   [current].Value,
   ISNULL([next].Value, 0) - [current].Value
FROM
   sourceTable       AS [current]
LEFT JOIN
   sourceTable       AS [next]
      ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)

EDIT:

Thinking about it, using a subquery in the select (ala Quassnoi’s answer) may be more efficient. I would trial different versions, and look at the execution plans to see which would perform best on the size of data set that you have…

EDIT2:

I still see this garnering votes, though it’s unlikely many people still use SQL Server 2005.

If you have access to Windowed Functions such as LEAD(), then use that instead…

SELECT
  RowInt,
  Value,
  LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - Value
FROM
  sourceTable

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Insert results of a stored procedure into a temporary table
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. How to update two tables in one statement in SQL Server 2005?
  5. Create a date from day month and year with T-SQL
  6. How important is the order of columns in indexes?
  7. How to return multiple values in one column (T-SQL)?
  8. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  9. SQL – Subtracting a depleting value from rows
  10. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  11. Is it possible to select sql server data using column ordinal position
  12. SELECT FOR UPDATE with SQL Server
  13. How do I group on continuous ranges
  14. How can a LEFT OUTER JOIN return more records than exist in the left table?
  15. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  16. Dynamic SQL to generate column names?
  17. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  18. Column name or number of supplied values does not match table definition
  19. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  20. How to split string using delimiter char using T-SQL?
  21. How to find consecutive rows based on the value of a column?
  22. OPENXML with xmlns:dt
  23. SQL server select distinct rows using most recent value only
  24. How to escape square brackets inside square brackets for field name
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. Eliminate and reduce overlapping date ranges
  27. Cumulative Total in MS Sql server [duplicate]
  28. Why do SQL Server Scalar-valued functions get slower?
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment