;WITH n(Number,NumberDiff, ROW) AS (SELECT Number, NumberDifference, ROW_NUMBER() OVER (ORDER BY Number) FROM Test)
UPDATE b SET NumberDiff=b.Number-a.Number
FROM n a INNER JOIN n b ON a.row=b.row-1

UPDATE Test SET NumberDifference=1 WHERE NumberDifference IS NULL

quote:

---

Originally posted by robvolk

I got this to work:

;WITH n(Number,NumberDiff, ROW) AS (SELECT Number, NumberDifference, ROW_NUMBER() OVER (ORDER BY Number) FROM Test)
UPDATE b SET NumberDiff=b.Number-a.Number
FROM n a INNER JOIN n b ON a.row=b.row-1

UPDATE Test SET NumberDifference=1 WHERE NumberDifference IS NULL

---

quote:

---

Thank you, it works very well. Could you please provide some explanation as well?

---

quote:

---

Originally posted by jcelko

Please post DDL, so that people do not have to guess what the keys, constraINTEGER NOT NULL,s, Declarative Referential INTEGER NOT NULL,egrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

It would also help if you knew the differences between rows and records, fields and columns. The Delta is a computed column, so why store it?

CREATE TABLE Test
(test_date DATE NOT NULL PRIMARY KEY,
test_score INTEGER);

>> the table has fields [sic] test_score and test_date filled. The test_date column has data values for each day in the calender including weekends. The test_delta is empty [sic: NULL, empty is not a numeric value]. <<

Here is the syntax for table insertion:

INSERT INTO Tests
VALUES (1, '2011- 01-01'), -- ISO-8601 date formats, please
(5, '2011- 01-02'),
(12, '2011- 01-03'),
(15, '2011- 01-04');

Use a VIEW that will always be correct and not need updating when something changes.

CREATE VIEW Test_Deltas ( test_date,test_score, test_delta)
AS
SELECT T1.test_date, T1.test_score,
(T2.test_score - T1.test_score) AS test_delta
FROM Tests AS T1, Tests AS T2
WHERE T1.test_date = DATEADD(DAY, -1, T2.test_date);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

---

quote:

---

thank you for the solution and comments. They don't provide the solution requested though. Please read it carefully.

---

quote:

---

Originally posted by robvolk

I used a common table expression (CTE) to add a sequential row number to the results. I then joined the CTE back to itself, offsetting the row number by 1, to do the UPDATE. The 2nd UPDATE catches the first row that doesn't have a previous row.

CTEs and ROW_NUMBER() are documented in SQL Server Books Online if you want more information about them.

---

quote:

---

great, thank you much. Miraculously, i had similar question on an interview

---