Datediff between two dates on different rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Datediff between two dates on different rows

Author

Topic

pmjwales
Starting Member

2 Posts

Posted - 2004-10-22 : 10:48:28

Hopefully someone can help me, I am trying to find the time difference between two datetimes in a dataset where the dates are on sequential rows and in different columns.

ie
tblJob_History

job job_start_dt job_end_dt
aaa01 01/01/2004 20:00 01/01/2004 20:30
aaa02 01/01/2004 20:34 01/01/2004 21:45

I am looking to find the difference between job aaa01 endtime and job aaa02 starttime, so the result needs to be 4 mins.

Any help would be greatfully recieved.

Thanks

Pete

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 10:56:46

this should help:


declare @table table (job varchar(10), job_start_dt datetime,  job_end_dt datetime)
insert into @table
select 'aaa01', '01/01/2004 20:00', '01/01/2004 20:30' union all 
select 'aaa02', '01/01/2004 20:34', '01/01/2004 21:45' union all 
select 'aaa03', '01/01/2004 21:50', '01/01/2004 22:15' union all 
select 'aaa04', '01/01/2004 22:35', '01/01/2004 23:05'

select coalesce(datediff(n, t1.job_end_dt, (select top 1 job_start_dt from @table where job > t1.job)),0) as diff 
from @table t1

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-25 : 12:27:00

Don't you need an ORDER BY in your sub select?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 12:35:15

no... why???

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-25 : 13:01:13

Yes an ORDER BY would be appropriate...
/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-10-25 : 13:20:06

"no... why???"


(select top 1 job_start_dt from @table where job > t1.job)

will pick a row at random otherwise to DATEDIFF against, won't it?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-25 : 13:42:02

damn it... you're right... forgot about no order in db...
thanx for the correction.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 01:09:07

Hehehehe ... I never wrote a bug in my code. Not ever ...

Kristen

pmjwales
Starting Member

2 Posts

Posted - 2004-10-26 : 04:33:08

Thanks very much everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources