Post reply

how to count record value based on previous record?

  • June 30, 2006 at 1:49 am

    #94471

    Hello!

    I'm using MS SQL Server 2000.

    I would like to make a query (not stored procedure) where I can count a record value from the previous(or next) record value.

    /Table Event/

    EventID StartDate

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

    2 2006-06-20 13:30:10

    4 2006-06-20 13:30:46

    11 2006-06-20 13:31:23

    5 2006-06-20 13:32:03

    4 2006-06-20 13:32:14

    ...

    so, i would like to show three fields, eventid,startdate, and the difference (in seconds) between each event. i cannot use stored procedures. is it possible to reach other records value?

    thanks for your help!

  • June 30, 2006 at 4:26 am

    #646920

    Szabo

    This will give you the event id, the start time and the start time of the previous event.  You can play about with datediff to get the actual time difference, or you can have your front end do that for you.  Not sure what performance will be like if you have a lot of rows in your table.

    John

    create table #table (eventid int, eventdate datetime)

    insert into #table values (2, '20-06-06 13:30:10.000')

    insert into #table values (4, '20-06-06 13:30:46.000')

    insert into #table values (11, '20-06-06 13:31:23.000')

    insert into #table values (5, '20-06-06 13:32:03.000')

    insert into #table values (4, '20-06-06 13:32:14.000')

    select t1.eventid, t1.eventdate,

     (select (max(t2.eventdate) from #table t2 where t2.eventdate < t1.eventdate) as Previous

    from #table t1

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

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