Time Calculation Query

From: Kevin Munro (kevin_at_c3amulet.com)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 14:47:24 -0000

Hello, I'm having problems calculation time between adjacent transactions,
hopefully this DDL explains what I'm doing and what results I am looking
for. Any help much appreciated.

Thanks, Kevin.

-- first off create a source transaction table and a destiantion transaction
table
create table #trans1 (t datetime)
create table #trans2 (t datetime,r real)

-- insert some transactions
insert into #trans1 values ('2005-01-09 17:26:13')
insert into #trans1 values ('2005-01-09 17:27:14')
insert into #trans1 values ('2005-01-09 17:28:15')
insert into #trans1 values ('2005-01-09 17:29:16')

-- display the transactions
select * from #trans1 order by t desc

-- and the above query returns these
-- 2005-01-09 17:29:16.000
-- 2005-01-09 17:28:15.000
-- 2005-01-09 17:27:14.000
-- 2005-01-09 17:26:13.000

-- now I want to find the time difference in seconds between adjacent
records
-- and store this in a new table with the transaction time of the first
record

-- i.e. trans2 woiuld container t and r columns of

-- 2005-01-09 17:29:16.000 45+16=61 seconds
-- 2005-01-09 17:28:15.000 46+15=61 seconds
-- 2005-01-09 17:27:14.000 47+14=61 seconds
-- 2005-01-09 17:26:13.000 45+14=59 seconds

-- it's the 61, 61, 61 and 59 seconds between adjacent pairs I can't see how
to calculate!

drop table #trans1
drop table #trans2