# calculating the difference between two rows - Microsoft SQL / MS SQL Server

Every weekday a row gets appended to my table and I would like to calculate the difference between the current row and previous row for each column. My data looks like this: count(1)count(2)count(3)count(4)load_date 7148 7148 6028 7148 7/30/2003 7159 7159 6036 7159 8/1/2003 7159 7159 6036 7159 8/2/2003 7159 7159 6036 7159 8/3/2003 7159 7159 6036 7159 8/4/2003 7160 7160 6037 7160 8/5/2003 7166 7166 6038 7166 8/6/2003 So the results would be: 11 11 8 11 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 6 6 1 6 What is the ...

1. ## calculating the difference between two rows

Every weekday a row gets appended to my table and I would
like to calculate the difference between the current row
and previous row for each column. My data looks like this:

7148 7148 6028 7148 7/30/2003
7159 7159 6036 7159 8/1/2003
7159 7159 6036 7159 8/2/2003
7159 7159 6036 7159 8/3/2003
7159 7159 6036 7159 8/4/2003
7160 7160 6037 7160 8/5/2003
7166 7166 6038 7166 8/6/2003

So the results would be:

11 11 8 11
0 0 0 0
0 0 0 0
0 0 0 0
1 1 1 1
6 6 1 6

What is the function for this calculation?

Thanks in advance for any help.

Helena

Helena Guest

2. ## Re: calculating the difference between two rows

Helena,

CREATE TABLE counts (
count_1 INT NOT NULL,
count_2 INT NOT NULL,
count_3 INT NOT NULL,
count_4 INT NOT NULL,
load_dt DATETIME NOT NULL PRIMARY KEY);

INSERT INTO counts VALUES (7148,7148,6028,7148,'2003-07-30');
INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-01');
INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-02');
INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-03');
INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-04');
INSERT INTO counts VALUES (7160,7160,6037,7160,'2003-08-05');
INSERT INTO counts VALUES (7166,7166,6038,7166,'2003-08-06');

SELECT
curr.count_1 - prev.count_1 AS chg_1,
curr.count_2 - prev.count_2 AS chg_1,
curr.count_3 - prev.count_3 AS chg_1,
curr.count_4 - prev.count_4 AS chg_1
FROM
counts AS curr,
counts AS prev
WHERE
FROM counts
ORDER BY

Hope that helps,
Rich

"Helena Silva" <com> wrote in message
news:008301c35c3a\$e836f440\$gbl...

Rich Guest

3. ## Re: calculating the difference between two rows

If we can assume that you haver a row for every day, then you can use
this:

(T.count_1 - Y.count_1) AS chg_1,
(T.count_2 - Y.count_2) AS chg_2,
(T.count_3 - Y.count_3) AS chg_3,
(T.count_4 - Y.count_4) AS chg_4
FROM Counts AS T, Counts AS Y -- today, yesterday

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Joe Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•