DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Multi-row calculations

  1. 04-20-2000, 10:32 AM #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!
    Reply With Quote Reply With Quote
  2. 04-20-2000, 02:44 PM #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!

    Reply With Quote Reply With Quote
  3. 04-20-2000, 09:22 PM #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!
    >

    Reply With Quote Reply With Quote
  4. 04-21-2000, 08:50 AM #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!


    Reply With Quote Reply With Quote
  5. 04-24-2000, 08:12 AM #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!

    Reply With Quote Reply With Quote
  6. 04-24-2000, 10:27 AM #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!
    >>
    >

    Reply With Quote Reply With Quote
  7. 04-27-2000, 08:08 AM #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
    >

    Reply With Quote Reply With Quote
  8. 04-27-2000, 11:14 AM #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
    >>
    >

    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center