Subtracting values from two different records in a set of records 1

[LeonelSanchezJr]

Here's a sample of my data: (Time is in seconds)

RecordNumber Start_Time Stop_Time
1 43240 43445
2 43630 43730
3 44120 44290
4 44650 44795

I have to calculate the WAIT TIME from records like these.
The WAIT TIME would be the Stop_Time From Record #1
minus the Start_Time from Record #2. The Stop_Time From Record #2 minus the Start_Time from Record #3. The Stop_Time From Record #3 minus the Start_Time from Record #4.

I would get the sum of all of these individual Wait Times and get one total. How can I do ths?

 

[fluteplr]

select sum(t2.starttime - t1.stoptime) from
mytable t1, mytable t2 where
t2.recordnumber = t1.recordnumber + 1

 

[LeonelSanchezJr]

That did not work. Thank you for your efforts anyway.

 

[fluteplr]

If you told us what did not work we could try to help you again. Is recordnumber actually a field called recordnumber the way you described it in your first post?

 

[LeonelSanchezJr]

Well, the WHERE clause is giving me a problem.

I wrote the syntax as such:

Select SUM(Table2.StopTime - Table1.StartTime) AS WaitTime
From MyTable Table1, MyTable Table2
Where RecNo(Table2) = (RecNo(Table1) + 1)

because the original syntax in the WHERE clause did not work with the t2.recordnumber = t1.recordnumber + 1

So, the numbers are not adding up correctly.

 

[LeonelSanchezJr]

IT WORKED!!!

THANKS.....