Multi-row calculations

 DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

1. Bob Hines Guest

## Multi-row calculations

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!

2. Daniel Reber Guest

## Re: Multi-row calculations

Bob,
I have a solution. You will probaly have to make some changes depending
on how you want to use the data.

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

CREATE TABLE #results (
first DATETIME NOT NULL,
second DATETIME NOT NULL,
duration REAL NOT NULL,
difference REAL NULL
)

DECLARE @first as datetime
DECLARE @second as datetime
DECLARE @firstvalue as int
DECLARE @secondvalue as int
DECLARE @count as int
DECLARE @loop as int

SET NOCOUNT ON

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

SELECT @count =(SELECT COUNT(*) FROM #table)-1

DECLARE curfirst CURSOR FOR
SELECT * FROM #table
OPEN curfirst

DECLARE cursecond SCROLL CURSOR FOR
SELECT * FROM #table
OPEN cursecond
FETCH FIRST FROM cursecond INTO @first,@firstvalue

SELECT @loop = 0

WHILE @loop < @count
BEGIN
FETCH NEXT FROM curfirst INTO @first,@firstvalue
FETCH NEXT FROM cursecond INTO @second,@secondvalue
INSERT INTO #results
VALUES (@first, @second,(DATEDIFF(ss, @first,@second)/60),@secondvalue
- @firstvalue)
SELECT @loop = @loop+1
END

CLOSE curfirst
DEALLOCATE curfirst
CLOSE cursecond
DEALLOCATE cursecond

SET NOCOUNT OFF

SELECT * FROM #results

DROP TABLE #Table
DROP TABLE #results

Hope this helps.

Danile Reber, MCP

"Bob Hines" <bobhi@pectech.com> wrote:
>
>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!

3. Bob Hines Guest

## Re: Multi-row calculations

Thanks for the response. I was hoping to avoid using a cursor.
I think I've come up with a solution that makes use of a second temporary
table. I create another table with an additional identity column, insert
the records from the original, then change the where clause to exclude all
but the next row. More specifically...

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

CREATE TABLE #TempTable2 (
Id INT IDENTITY(1,1),
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)

INSERT INTO #TempTable2
SELECT * FROM #TempTable

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 #TempTable2 first, #TempTable2 second
WHERE first.Id = (second.Id - 1)

DROP TABLE #TempTable
DROP TABLE #TempTable2

Any thoughts?

"Daniel Reber" <daniel@domain-group.com> wrote:
>
>Bob,
> I have a solution. You will probaly have to make some changes depending
>on how you want to use the data.
>
>CREATE TABLE #table (
> DateTime DATETIME NOT NULL,
> Value REAL NULL
>)
>
>CREATE TABLE #results (
> first DATETIME NOT NULL,
> second DATETIME NOT NULL,
> duration REAL NOT NULL,
> difference REAL NULL
>)
>
>DECLARE @first as datetime
>DECLARE @second as datetime
>DECLARE @firstvalue as int
>DECLARE @secondvalue as int
>DECLARE @count as int
>DECLARE @loop as int
>
>SET NOCOUNT ON
>
>INSERT INTO #Table VALUES ('4/10/2000 6:30am', 0)
>INSERT INTO #Table VALUES ('4/10/2000 6:31am', 1)
>INSERT INTO #Table VALUES ('4/10/2000 6:32am', 0)
>INSERT INTO #Table VALUES ('4/10/2000 6:33am', 1)
>INSERT INTO #Table VALUES ('4/10/2000 6:34am', 0)
>INSERT INTO #Table VALUES ('4/10/2000 6:35am', NULL)
>INSERT INTO #Table VALUES ('4/10/2000 6:36am', 0)
>INSERT INTO #Table VALUES ('4/10/2000 6:37am', 1)
>INSERT INTO #Table VALUES ('4/10/2000 6:38am', 0)
>INSERT INTO #Table VALUES ('4/10/2000 6:39am', 1)
>INSERT INTO #Table VALUES ('4/10/2000 6:40am', NULL)
>INSERT INTO #Table VALUES ('4/10/2000 6:41am', 1)
>INSERT INTO #Table VALUES ('4/10/2000 6:42am', 0)
>
>SELECT @count =(SELECT COUNT(*) FROM #table)-1
>
>DECLARE curfirst CURSOR FOR
>SELECT * FROM #table
>OPEN curfirst
>
>
>DECLARE cursecond SCROLL CURSOR FOR
>SELECT * FROM #table
>OPEN cursecond
>FETCH FIRST FROM cursecond INTO @first,@firstvalue
>
>SELECT @loop = 0
>
>WHILE @loop < @count
> BEGIN
> FETCH NEXT FROM curfirst INTO @first,@firstvalue
> FETCH NEXT FROM cursecond INTO @second,@secondvalue
> INSERT INTO #results
> VALUES (@first, @second,(DATEDIFF(ss, @first,@second)/60),@secondvalue
>- @firstvalue)
> SELECT @loop = @loop+1
> END
>
>CLOSE curfirst
>DEALLOCATE curfirst
>CLOSE cursecond
>DEALLOCATE cursecond
>
>SET NOCOUNT OFF
>
>SELECT * FROM #results
>
>DROP TABLE #Table
>DROP TABLE #results
>
>Hope this helps.
>
>Danile Reber, MCP
>
>
>"Bob Hines" <bobhi@pectech.com> wrote:
>>
>>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!

>

4. Michael Levy Guest

## Re: Multi-row calculations

Bob,

Try this:

SELECT
first.DateTime as first,
(SELECT TOP 1 datetime FROM #temptable tt WHERE tt.datetime >
first.datetime) AS second,
DATEDIFF(ss, first.DateTime, (SELECT TOP 1 datetime FROM #temptable
tt WHERE tt.datetime > first.datetime)) / 60 as duration,
(SELECT TOP 1 value FROM #temptable tt WHERE tt.datetime >
first.datetime) - first.Value as difference
FROM #TempTable first

Not the prettiest thing but it's an alternative

-Mike
--
Michael Levy MCSD, MCDBA, MCT
Consultant
GA Sullivan
michaell@gasullivan.com

"Bob Hines" <bobhi@pectech.com> wrote in message
news:38ff150a\$1@news.devx.com...
>
> 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!

5. Iulian Bulucea Guest

## Re: Multi-row calculations

Hi Bob,

One of the easier ways is to create an indexed ID column in the temporary
table and, assuming that the records have been inserted sequentially, you
can execute the following query:

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 INNER JOIN TempTable second
ON first.ID = second.ID - 1

The results will be displayed exactly as you wanted to see them.

Good luck,

Iulian

"Bob Hines" <bobhi@pectech.com> wrote:
>
>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!

6. Daniel Reber Guest

## Re: Multi-row calculations

A self join does look like the best solution. The only problem I foresee is
your insert statments. If you will be using the data in an application, your
recordset won't be populated correctly unless you use SET NOCOUNT ON before
the inserts, and SET NOCOUNT OFF after the inserts.

Daniel Reber, MCP

"Bob Hines" <bobhi@pectech.com> wrote:
>
>Thanks for the response. I was hoping to avoid using a cursor.
>I think I've come up with a solution that makes use of a second temporary
>table. I create another table with an additional identity column, insert
>the records from the original, then change the where clause to exclude all
>but the next row. More specifically...
>
>CREATE TABLE #TempTable (
> DateTime DATETIME NOT NULL,
> Value REAL NULL
>)
>
>CREATE TABLE #TempTable2 (
> Id INT IDENTITY(1,1),
> 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)
>
>INSERT INTO #TempTable2
> SELECT * FROM #TempTable
>
>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 #TempTable2 first, #TempTable2 second
>WHERE first.Id = (second.Id - 1)
>
>DROP TABLE #TempTable
>DROP TABLE #TempTable2
>
>Any thoughts?
>
>"Daniel Reber" <daniel@domain-group.com> wrote:
>>
>>Bob,
>> I have a solution. You will probaly have to make some changes depending
>>on how you want to use the data.
>>
>>CREATE TABLE #table (
>> DateTime DATETIME NOT NULL,
>> Value REAL NULL
>>)
>>
>>CREATE TABLE #results (
>> first DATETIME NOT NULL,
>> second DATETIME NOT NULL,
>> duration REAL NOT NULL,
>> difference REAL NULL
>>)
>>
>>DECLARE @first as datetime
>>DECLARE @second as datetime
>>DECLARE @firstvalue as int
>>DECLARE @secondvalue as int
>>DECLARE @count as int
>>DECLARE @loop as int
>>
>>SET NOCOUNT ON
>>
>>INSERT INTO #Table VALUES ('4/10/2000 6:30am', 0)
>>INSERT INTO #Table VALUES ('4/10/2000 6:31am', 1)
>>INSERT INTO #Table VALUES ('4/10/2000 6:32am', 0)
>>INSERT INTO #Table VALUES ('4/10/2000 6:33am', 1)
>>INSERT INTO #Table VALUES ('4/10/2000 6:34am', 0)
>>INSERT INTO #Table VALUES ('4/10/2000 6:35am', NULL)
>>INSERT INTO #Table VALUES ('4/10/2000 6:36am', 0)
>>INSERT INTO #Table VALUES ('4/10/2000 6:37am', 1)
>>INSERT INTO #Table VALUES ('4/10/2000 6:38am', 0)
>>INSERT INTO #Table VALUES ('4/10/2000 6:39am', 1)
>>INSERT INTO #Table VALUES ('4/10/2000 6:40am', NULL)
>>INSERT INTO #Table VALUES ('4/10/2000 6:41am', 1)
>>INSERT INTO #Table VALUES ('4/10/2000 6:42am', 0)
>>
>>SELECT @count =(SELECT COUNT(*) FROM #table)-1
>>
>>DECLARE curfirst CURSOR FOR
>>SELECT * FROM #table
>>OPEN curfirst
>>
>>
>>DECLARE cursecond SCROLL CURSOR FOR
>>SELECT * FROM #table
>>OPEN cursecond
>>FETCH FIRST FROM cursecond INTO @first,@firstvalue
>>
>>SELECT @loop = 0
>>
>>WHILE @loop < @count
>> BEGIN
>> FETCH NEXT FROM curfirst INTO @first,@firstvalue
>> FETCH NEXT FROM cursecond INTO @second,@secondvalue
>> INSERT INTO #results
>> VALUES (@first, @second,(DATEDIFF(ss, @first,@second)/60),@secondvalue
>>- @firstvalue)
>> SELECT @loop = @loop+1
>> END
>>
>>CLOSE curfirst
>>DEALLOCATE curfirst
>>CLOSE cursecond
>>DEALLOCATE cursecond
>>
>>SET NOCOUNT OFF
>>
>>SELECT * FROM #results
>>
>>DROP TABLE #Table
>>DROP TABLE #results
>>
>>Hope this helps.
>>
>>Danile Reber, MCP
>>
>>
>>"Bob Hines" <bobhi@pectech.com> wrote:
>>>
>>>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!

>>

>

7. Bob Hines Guest

## Re: Multi-row calculations

I'm a little confused. Why would turning NOCOUNT on or off affect any inserts
into the table? I've seen it have affect on MS Access where Access thinks
a stored procedure is done before it's really done but I don't see how it
would have affects in this case.

"Daniel Reber" <daniel@domain-group.com> wrote:
>
>A self join does look like the best solution. The only problem I foresee

is
>your insert statments. If you will be using the data in an application,

your
>recordset won't be populated correctly unless you use SET NOCOUNT ON before
>the inserts, and SET NOCOUNT OFF after the inserts.
>
>Daniel Reber, MCP
>

8. Daniel Reber Guest

## Re: Multi-row calculations

It all depends on what the data is used for. If it is just being used in SQL
Server, then it probably won't be needed. But if the data is being sent to
a VB applicaion to a ADO recordset, or MS Access like you said, then you
will get an empty recordset because control will be returned to the application
before the data has been retrieved. Again, if the data will be used only
in SQL, then this is a mute point.

Daniel Reber, MCP

"Bob Hines" <bobhi@pectech.com> wrote:
>
>I'm a little confused. Why would turning NOCOUNT on or off affect any inserts
>into the table? I've seen it have affect on MS Access where Access thinks
>a stored procedure is done before it's really done but I don't see how it
>would have affects in this case.
>
>"Daniel Reber" <daniel@domain-group.com> wrote:
>>
>>A self join does look like the best solution. The only problem I foresee

>is
>>your insert statments. If you will be using the data in an application,

>your
>>recordset won't be populated correctly unless you use SET NOCOUNT ON before
>>the inserts, and SET NOCOUNT OFF after the inserts.
>>
>>Daniel Reber, MCP
>>

>

#### Posting Permissions

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

 FAQ Latest Articles Java .NET XML Database Enterprise