SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd and 4th and so on

  • Kenny Jozi

    SSCrazy

    Points: 2004

    I need help with SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd and 4th and so forth on Column Datatime..

    I used Row_Number() Over on 2005 and 2008 and it works

    Datatime Column 1 Column 2 Column 3

    2012-03-03 14:20:35.000 1111222223334 50051 1

    2012-03-03 15:17:17.000 1111222223334 50051 1

    2012-03-03 15:26:13.000 1111222223334 50051 1

    2012-03-03 15:27:40.000 1111222223334 50051 1

    2012-03-03 15:33:28.000 1111222223334 50051 1

    2012-03-03 15:35:44.000 1111222223334 50051 1

    2012-03-03 15:38:55.000 1111222223334 50051 1

    2012-03-12 12:28:41.000 1111222223334 50051 1

    2012-03-12 12:31:00.000 1111222223334 50051 1

    2012-04-05 08:29:45.000 1111222223334 50051 1

    2012-04-05 08:32:40.000 1111222223334 50051 1

    2012-04-05 08:34:00.000 1111222223334 50051 1

    2012-03-15 14:18:05.000 7406140316086 34544 1

    2012-03-15 14:30:03.000 7406140316086 34544 1

    2012-02-24 14:41:24.000 BN422019 30417 1

    2012-02-24 15:03:26.000 BN422019 30417 1

    I need the query to check the difference and return results if difference is more than an hour else return 1 row

    Expected results

    Datatime Column 1 Column 2 Column 3

    2012-03-03 14:20:35.000 1111222223334 50051 1

    2012-03-03 15:26:13.000 1111222223334 50051 1

    2012-03-12 12:28:41.000 1111222223334 50051 1

    2012-04-05 08:29:45.000 1111222223334 50051 1

    2012-03-15 14:18:05.000 7406140316086 34544 1

    2012-02-24 14:41:24.000 BN422019 30417 1

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • Kenny Jozi

    SSCrazy

    Points: 2004

    this is the Tsql i was using

    SELECT [Column 1], [Datatime],[Column 2] , [Column 3]

    FROM [test].[dbo].[upload] S

    WHERE NOT EXISTS (SELECT *

    FROM [test].[dbo].[upload] x

    WHERE x.[Column 2] = S.[Column 2]

    and x.[Column 1] = S.[Column 1]

    AND DATEDIFF(HH,S.Datatime,1) > 0

    or (x.Datatime = S.Datatime AND

    x.[Column 3]< S.[Column 3])

    )

    order by S.[Column 1]

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • ChrisM@Work

    SSC Guru

    Points: 186054

    The description's a bit vague so this is something of a guess:

    SELECT [Column 1], [Datatime],[Column 2] , [Column 3]

    FROM [test].[dbo].[upload] S

    WHERE NOT EXISTS (

    SELECT 1

    FROM [test].[dbo].[upload] x

    WHERE x.[Column 2] = S.[Column 2]

    AND x.[Column 1] = S.[Column 1]

    AND ABS(DATEDIFF(HH,x.Datatime,s.Datatime)) < 1

    )

    order by S.[Column 1]

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Sergiy

    SSC Guru

    Points: 109670

    kenneth.mofokeng (7/5/2012)


    I need help with SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd and 4th and so forth on Column Datatime..

    I used Row_Number() Over on 2005 and 2008 and it works

    OVER() is pretty much a different look for a good old table variable.

    Try something like this:

    DECLARE @TempTable TABLE (

    N int IDENTITY(1,1) PRIMARY KEY,

    [Datatime] ... ,

    [Column 1] ... ,

    [Column 2] ... ,

    [Column 3] ... ,

    )

    INSERT INTO @TempTable

    ([Datatime], [Column 1], [Column 2], [Column 3])

    SELECT [Datatime], [Column 1], [Column 2], [Column 3]

    FROM YourTable

    WHERE {define your range here}

    order by [Datatime]

    SELECT T2.DataTime - T1.DataTime as TimeDiff, T1.[Datatime], T1.[Column 1], T1.[Column 2], T1.[Column 3]

    FROM @TempTable T1

    INNER JOIN @TempTable T2 ON T2.N = T1.N + 1

    WHERE T2.DataTime > DATEADD(hh, 1, T1.DataTime)

  • Kenny Jozi

    SSCrazy

    Points: 2004

    ChrisM@Work Sergily , πŸ™‚ Thank you guys very much, I used both your suggestions and got Desired results πŸ˜‰

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply