Professional Web Applications Themes

SQL - difference between 2 rows?? - Microsoft SQL / MS SQL Server

hi! i was having trouble getting a handle on this one...i created a table to log 'selects' in order to measure capacity of an inquiry-only system....my problem is i need to subtract one row's datetime from the next row's datetime in order to get the elapsed time between the two...any help is greatly appreciated! create table perfctr (perfid int identity, spid int not null, ts datetime not null, vehid int ) here are my rows: 11 56 8/8/2003 2:20:01.22.340 PM 123 12 57 8/8/2003 2:20:01.22.450 PM 124 13 56 8/8/2003 2:20:11.32.780 PM 125 14 57 8/8/2003 2:21:06.44.134 PM 125 15 ...

  1. #1

    Default SQL - difference between 2 rows??

    hi! i was having trouble getting a handle on this one...i created a table
    to log 'selects' in order to measure capacity of an inquiry-only
    system....my problem is i need to subtract one row's datetime from the next
    row's datetime in order to get the elapsed time between the two...any help
    is greatly appreciated!

    create table perfctr (perfid int identity, spid int not null, ts datetime
    not null, vehid int )

    here are my rows:

    11 56 8/8/2003 2:20:01.22.340 PM 123
    12 57 8/8/2003 2:20:01.22.450 PM 124
    13 56 8/8/2003 2:20:11.32.780 PM 125
    14 57 8/8/2003 2:21:06.44.134 PM 125
    15 56 8/8/2003 2:20:12.52.442 PM 125
    16 56 8/8/2003 2:20:14.37.770 PM 126
    11 56 8/8/2003 2:21:23.26.640 PM 128

    i need the report to show (nnnn is the endtime - startime):

    spid elapsed starttime endtime

    56 nnnnn 8/8/2003 2:20:01.22.340 8/8/2003 2:20:11.32.780 PM
    56 nnnnn 8/8/2003 2:20:12.52.442 8/8/2003 2:20:14.37.770 PM
    57 nnnnn 8/8/2003 2:20:01.22.450 8/8/2003 2:21:06.44.134 PM

    is this possible? thanks, chester



    chet Guest

  2. #2

    Default Re: SQL - difference between 2 rows??

    select spid,datediff(ms,starttime,endtime) as elapsed, starttime,endtime
    from table
    order by spid

    That would be for milliseconds, you could replace ms with ss to get seconds.

    HTH

    --
    Ray Higdon MCSE, MCDBA, CCNA
    ---
    "chet gwin" <rr.com> wrote in message
    news:6Wf%a.4996$austin.rr.com... 
    next 


    Ray Guest

  3. #3

    Default Re: SQL - difference between 2 rows??

    thanks....the only problem is the table only has - (spid, ts)!!

    the idea is that the table grows sequential, linear....so, for spid = 56,
    that is his first event (startime)...then the next time i encounter spid=56,
    this would be his last event (stoptime)...there could be spids that started
    but did not end before the trace was stopped.

    i have 50 spids in the table with 10000 total rows....

    the trick is how to grab 1 row which is the startime, scan until you get the
    next spid match and use this as the endtime (and dont use that one as you
    progress down the table), and then produce the single row of (spid /
    elapsed / start / stop).

    (also, my data showed 11 as the last row, it s/b 17)....thanks, chester


    "Ray Higdon" <com> wrote in message
    news:phx.gbl... 
    seconds. [/ref]
    table 
    > next [/ref]
    help [/ref]
    datetime [/ref]
    PM [/ref]
    PM [/ref]
    PM 
    >
    >[/ref]


    chet Guest

  4. #4

    Default Re: SQL - difference between 2 rows??

    depends if there are more than two SPID 56 entries
    if not, select MIN, MAX TS then do the math

     
    >seconds. [/ref]
    >table 
    >> next [/ref]
    >help [/ref]
    >datetime [/ref]
    >PM [/ref]
    >PM [/ref]
    >PM 
    >>
    >>[/ref]
    >[/ref]

    user Guest

  5. #5

    Default Re: SQL - difference between 2 rows??

    how about a start/stop flag?

     [/ref]
    >spid=56, [/ref]
    >started [/ref]
    >the [/ref]
    >starttime,endtime [/ref]
    >the [/ref]
    >2:20:11.32.780 [/ref]
    >2:20:14.37.770 [/ref]
    >2:21:06.44.134 
    >>[/ref]
    >[/ref]

    user Guest

Similar Threads

  1. Replies: 7
    Last Post: September 2nd, 05:44 AM
  2. Replies: 3
    Last Post: September 20th, 12:38 PM
  3. calculating the difference between two rows
    By Helena in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 6th, 09:43 PM
  4. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139