how to subtract values in consecutive rows?

SET NOCOUNT ON
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t
(
id INT IDENTITY
, [value] int)
INSERT INTO lfdsum_t values (10);
INSERT INTO lfdsum_t values (15);
INSERT INTO lfdsum_t values (35);
INSERT INTO lfdsum_t values (45);
INSERT INTO lfdsum_t values (50);

SELECT
ISNULL(a.id,b.id)
, a.[value]-b.value
FROM
lfdsum_t a
LEFT JOIN
lfdsum_t b
ON
a.id-1=b.id
DROP TABLE lfdsum_t
SET NOCOUNT OFF

----------- -----------
1 NULL
2 5
3 20
4 10
5 5

If you don't really care about the first row, you can change the LEFT JOIN to an INNER JOIN which yields



----------- -----------
2 5
3 20
4 10
5 5


--
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Okay, I was afraid, you mentioned something like this. Well, off head I can only think of a workaround. Guess what...<br /><pre id="code"><font face="courier" size="2" id="code"><br />SET NOCOUNT ON<br />IF OBJECT_ID('lfdsum_t') IS NOT NULL<br /> DROP TABLE lfdsum_t<br />GO<br />CREATE TABLE lfdsum_t <br />(id INT <br />, [value] int)<br />INSERT INTO lfdsum_t values (1,10);<br />INSERT INTO lfdsum_t values (2,15);<br />INSERT INTO lfdsum_t values (3,35);<br />INSERT INTO lfdsum_t values (5,45);<br />INSERT INTO lfdsum_t values (7,50);<br />CREATE TABLE #workaround<br />(<br />ID_tmp INT IDENTITY<br />, ID INT<br />, [value] INT<br />)<br />INSERT INTO #workaround (id, [value])<br />SELECT id, [value] FROM lfdsum_t ORDER BY ID<br />SELECT<br /> a.id<br />, a.[value]-b.value<br />FROM<br /> #workaround a<br />LEFT JOIN<br /> #workaround b<br />ONa.id_tmp-1=b.id_tmp<br />DROP TABLE lfdsum_t, #workaround<br />SET NOCOUNT OFF<br /><br />id <br />----------- ----------- <br />1 NULL<br />2 5<br />3 20<br />5 10<br />7 5<br /><br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />

You can use a CURSOR [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />

FWIW, here's another solution. Can you test the performance for me?


SET NOCOUNT ON
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t
(id INT
, [value] int)
INSERT INTO lfdsum_t values (1,10);
INSERT INTO lfdsum_t values (2,15);
INSERT INTO lfdsum_t values (3,35);
INSERT INTO lfdsum_t values (5,45);
INSERT INTO lfdsum_t values (7,50);

SELECT
a.id
, [value]
, [value] -( SELECT TOP 1 b.[value]
FROM lfdsum_t b
WHERE b.id < a.id
ORDER BY b.id DESC ) x
FROM
lfdsum_t a
DROP TABLE lfdsum_t
SET NOCOUNT OFF


id value x
----------- ----------- -----------
1 10 NULL
2 15 5
3 35 20
5 45 10
7 50 5


--
Frank Kalis
SQL Server MVP
http://www.insidesql.de

[quote user="tc1"]
I have a similar problem except I don't have an id column. Instead I want to use the row numbers to calculate consequtive valuse. Is this possible?
[/quote]
No problem, unless you have some column that can uniquely identify each row. Consider this:
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t
(key_col INT, [value] int)
INSERT INTO lfdsum_t values (1,10);
INSERT INTO lfdsum_t values (2,15);
INSERT INTO lfdsum_t values (3,35);
INSERT INTO lfdsum_t values (5,45);
INSERT INTO lfdsum_t values (7,50);

WITH myCTE (rn, [value])
AS
(SELECT ROW_NUMBER() OVER(ORDER BY key_col) AS rn, value
FROM lfdsum_t)SELECT
rn, [value] - (SELECT TOP 1 b.[value]
FROM myCTE b
WHERE b.rn < a.rn
ORDER BY b.rn DESC)
FROM myCTE aDROP
TABLE lfdsum_t rn
-------------------- -----------
1 NULL
2 5
3 20
4 10
5 5

[quote user="tc1"]
Thanks Frank. The only problem is I have an older version of SQL which doesn't have the ROW_NUMBER function. Do you know of an alternative way of doing this using SQL 8?
[/quote]
Sorry, I thought you're using SQL Server 2005 because you mentioned row number. []
Anyway, as long as you have a primary (or unique) key in your table, you should be able to use the solutions mentioned earlier in this thread, such as:SELECT
a.key_col, [value]
, [value]
-( SELECT TOP 1 b.[value]
FROM lfdsum_t b
WHERE b.key_col < a.key_col
ORDER BY b.key_col DESC ) x
FROM lfdsum_t a
One "problem" however might be the performance on larger tables. If that is the case, you can workaround this by using a temp table in which you insert the rows in the order in which they should be processed for your calculations. Something like:CREATE
TABLE #temp (key_col INT IDENTITY, [value] int)INSERT
INTO #temp ([value]) SELECT [value]
FROM lfdsum_t
ORDER BY key_colSELECT
a.key_col, [value]
, [value]
-( SELECT TOP 1 b.[value]
FROM #temp b
WHERE b.key_col < a.key_col
ORDER BY b.key_col DESC ) x
FROM #temp a
Both methods should work in SQL Server 2000.

[quote user="tc1"]
I have a similar problem except I don't have an id column. Instead I want to use the row numbers to calculate consequtive valuse. Is this possible?
[/quote]
Where do you want to show data?
If you want to show them in front end, then you can calculate it when present data using while loop