I have the following code snippet that returns two datetimes (each originally
on their own row, the difference between the two, and the difference between
two values (each associated with the original datetimes).

CREATE TABLE #TempTable (
DateTime DATETIME NOT NULL,
Value REAL NULL
)

INSERT INTO #TempTable VALUES ('4/10/2000 6:30am', 0)
INSERT INTO #TempTable VALUES ('4/10/2000 6:31am', 1)
INSERT INTO #TempTable VALUES ('4/10/2000 6:32am', 0)
INSERT INTO #TempTable VALUES ('4/10/2000 6:33am', 1)
INSERT INTO #TempTable VALUES ('4/10/2000 6:34am', 0)
INSERT INTO #TempTable VALUES ('4/10/2000 6:35am', NULL)
INSERT INTO #TempTable VALUES ('4/10/2000 6:36am', 0)
INSERT INTO #TempTable VALUES ('4/10/2000 6:37am', 1)
INSERT INTO #TempTable VALUES ('4/10/2000 6:38am', 0)
INSERT INTO #TempTable VALUES ('4/10/2000 6:39am', 1)
INSERT INTO #TempTable VALUES ('4/10/2000 6:40am', NULL)
INSERT INTO #TempTable VALUES ('4/10/2000 6:41am', 1)
INSERT INTO #TempTable VALUES ('4/10/2000 6:42am', 0)

SELECT first.DateTime as first,
second.DateTime as second,
DATEDIFF(ss, first.DateTime, second.DateTime) / 60 as duration,
second.Value - first.Value as difference
FROM #TempTable first, #TempTable second
WHERE first.DateTime < second.DateTime

DROP TABLE #TempTable

The result set looks like...
first second duration difference

--------------------------- --------------------------- ----------- ------------------------

Apr 10 2000 6:30AM Apr 10 2000 6:31AM 1 1.0

Apr 10 2000 6:30AM Apr 10 2000 6:32AM 2 0.0

Apr 10 2000 6:30AM Apr 10 2000 6:33AM 3 1.0

Apr 10 2000 6:30AM Apr 10 2000 6:34AM 4 0.0

Apr 10 2000 6:30AM Apr 10 2000 6:35AM 5 (null)

Apr 10 2000 6:30AM Apr 10 2000 6:36AM 6 0.0

Apr 10 2000 6:30AM Apr 10 2000 6:37AM 7 1.0

Apr 10 2000 6:30AM Apr 10 2000 6:38AM 8 0.0

Apr 10 2000 6:30AM Apr 10 2000 6:39AM 9 1.0

Apr 10 2000 6:30AM Apr 10 2000 6:40AM 10 (null)

Apr 10 2000 6:30AM Apr 10 2000 6:41AM 11 1.0

Apr 10 2000 6:30AM Apr 10 2000 6:42AM 12 0.0

Apr 10 2000 6:31AM Apr 10 2000 6:32AM 1 -1.0

Apr 10 2000 6:31AM Apr 10 2000 6:33AM 2 0.0

Apr 10 2000 6:31AM Apr 10 2000 6:34AM 3 -1.0

Apr 10 2000 6:31AM Apr 10 2000 6:35AM 4 (null)

Apr 10 2000 6:31AM Apr 10 2000 6:36AM 5 -1.0

Apr 10 2000 6:31AM Apr 10 2000 6:37AM 6 0.0

Apr 10 2000 6:31AM Apr 10 2000 6:38AM 7 -1.0

Apr 10 2000 6:31AM Apr 10 2000 6:39AM 8 0.0

Apr 10 2000 6:31AM Apr 10 2000 6:40AM 9 (null)

Apr 10 2000 6:31AM Apr 10 2000 6:41AM 10 0.0

Apr 10 2000 6:31AM Apr 10 2000 6:42AM 11 -1.0

Apr 10 2000 6:32AM Apr 10 2000 6:33AM 1 1.0

Apr 10 2000 6:32AM Apr 10 2000 6:34AM 2 0.0

Apr 10 2000 6:32AM Apr 10 2000 6:35AM 3 (null)

Apr 10 2000 6:32AM Apr 10 2000 6:36AM 4 0.0

Apr 10 2000 6:32AM Apr 10 2000 6:37AM 5 1.0

Apr 10 2000 6:32AM Apr 10 2000 6:38AM 6 0.0

Apr 10 2000 6:32AM Apr 10 2000 6:39AM 7 1.0

Apr 10 2000 6:32AM Apr 10 2000 6:40AM 8 (null)

Apr 10 2000 6:32AM Apr 10 2000 6:41AM 9 1.0

Apr 10 2000 6:32AM Apr 10 2000 6:42AM 10 0.0

Apr 10 2000 6:33AM Apr 10 2000 6:34AM 1 -1.0

Apr 10 2000 6:33AM Apr 10 2000 6:35AM 2 (null)

Apr 10 2000 6:33AM Apr 10 2000 6:36AM 3 -1.0

Apr 10 2000 6:33AM Apr 10 2000 6:37AM 4 0.0

Apr 10 2000 6:33AM Apr 10 2000 6:38AM 5 -1.0

Apr 10 2000 6:33AM Apr 10 2000 6:39AM 6 0.0

Apr 10 2000 6:33AM Apr 10 2000 6:40AM 7 (null)

Apr 10 2000 6:33AM Apr 10 2000 6:41AM 8 0.0

Apr 10 2000 6:33AM Apr 10 2000 6:42AM 9 -1.0

Apr 10 2000 6:34AM Apr 10 2000 6:35AM 1 (null)

Apr 10 2000 6:34AM Apr 10 2000 6:36AM 2 0.0

Apr 10 2000 6:34AM Apr 10 2000 6:37AM 3 1.0

Apr 10 2000 6:34AM Apr 10 2000 6:38AM 4 0.0

Apr 10 2000 6:34AM Apr 10 2000 6:39AM 5 1.0

Apr 10 2000 6:34AM Apr 10 2000 6:40AM 6 (null)

Apr 10 2000 6:34AM Apr 10 2000 6:41AM 7 1.0

Apr 10 2000 6:34AM Apr 10 2000 6:42AM 8 0.0

Apr 10 2000 6:35AM Apr 10 2000 6:36AM 1 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:37AM 2 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:38AM 3 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:39AM 4 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:40AM 5 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:41AM 6 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:42AM 7 (null)

Apr 10 2000 6:36AM Apr 10 2000 6:37AM 1 1.0

Apr 10 2000 6:36AM Apr 10 2000 6:38AM 2 0.0

Apr 10 2000 6:36AM Apr 10 2000 6:39AM 3 1.0

Apr 10 2000 6:36AM Apr 10 2000 6:40AM 4 (null)

Apr 10 2000 6:36AM Apr 10 2000 6:41AM 5 1.0

Apr 10 2000 6:36AM Apr 10 2000 6:42AM 6 0.0

Apr 10 2000 6:37AM Apr 10 2000 6:38AM 1 -1.0

Apr 10 2000 6:37AM Apr 10 2000 6:39AM 2 0.0

Apr 10 2000 6:37AM Apr 10 2000 6:40AM 3 (null)

Apr 10 2000 6:37AM Apr 10 2000 6:41AM 4 0.0

Apr 10 2000 6:37AM Apr 10 2000 6:42AM 5 -1.0

Apr 10 2000 6:38AM Apr 10 2000 6:39AM 1 1.0

Apr 10 2000 6:38AM Apr 10 2000 6:40AM 2 (null)

Apr 10 2000 6:38AM Apr 10 2000 6:41AM 3 1.0

Apr 10 2000 6:38AM Apr 10 2000 6:42AM 4 0.0

Apr 10 2000 6:39AM Apr 10 2000 6:40AM 1 (null)

Apr 10 2000 6:39AM Apr 10 2000 6:41AM 2 0.0

Apr 10 2000 6:39AM Apr 10 2000 6:42AM 3 -1.0

Apr 10 2000 6:40AM Apr 10 2000 6:41AM 1 (null)

Apr 10 2000 6:40AM Apr 10 2000 6:42AM 2 (null)

Apr 10 2000 6:41AM Apr 10 2000 6:42AM 1 -1.0



but I want it to be...

first second duration difference

--------------------------- --------------------------- ----------- ------------------------

Apr 10 2000 6:30AM Apr 10 2000 6:31AM 1 1.0

Apr 10 2000 6:31AM Apr 10 2000 6:32AM 1 -1.0

Apr 10 2000 6:32AM Apr 10 2000 6:33AM 1 1.0

Apr 10 2000 6:33AM Apr 10 2000 6:34AM 1 -1.0

Apr 10 2000 6:34AM Apr 10 2000 6:35AM 1 (null)

Apr 10 2000 6:35AM Apr 10 2000 6:36AM 1 (null)

Apr 10 2000 6:36AM Apr 10 2000 6:37AM 1 1.0

Apr 10 2000 6:37AM Apr 10 2000 6:38AM 1 -1.0

Apr 10 2000 6:38AM Apr 10 2000 6:39AM 1 1.0

Apr 10 2000 6:39AM Apr 10 2000 6:40AM 1 (null)

Apr 10 2000 6:40AM Apr 10 2000 6:41AM 1 (null)

Apr 10 2000 6:41AM Apr 10 2000 6:42AM 1 -1.0



I only want the differences between consecutive rows. The time differences
in the sample above are constant but will not be with the real world data.
Any suggestions on how to accomplish this.

Thanks!