Menu
I have a table like this:
rowInt Value 2 23 3 45 17 10 9 0 .... The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt:
SELECT * FROM myTable ORDER BY rowInt; This will list values by rowInt. How can get get the difference of Value between two rows with the result like this:
rowInt Value Diff 2 23 22 --45-23 3 45 -35 --10-45 9 0 -45 --0-45 17 10 10 -- 10-0 .... The table is in SQL 2005 (Miscrosoft)
863
To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function. This function allows you to obtain data from the previous record (based on an order criterion, which here is “ ORDER BY year ”).
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
The difference is calculated by using the particular row of the specified column and subtracting from it the previous value computed using the shift() method.
Example 1: Comparing rows of the same table. In the example, we are comparing the immediate rows to calculate the sales made on a day by comparing the amounts of two consecutive days. Syntax for inner join : SELECT column_name(s) FROM table1 t1 INNER JOIN table1 t2 on t1. column1 = t2.
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 
SELECT rowInt, Value, COALESCE( ( SELECT TOP 1 Value FROM myTable mi WHERE mi.rowInt > m.rowInt ORDER BY rowInt ), 0) - Value AS diff FROM myTable m ORDER BY rowInt If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
