find the time by subtracing from the next row

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

General SQL Server Forums

New to SQL Server Programming

find the time by subtracing from the next row

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-12 : 15:54:49

I have Table
ID,Time.
In my select I want to see
ID,Time,TimeToComplete.

the time to complete is the difference in time between two consecutive rows how can I do this.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 00:34:05

Refer the below link.. It will give you hint on how to start with.
http://www.sqlteam.com/article/joining-to-the-next-sequential-row

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 02:18:42

[code]
create table yourtable
(
id int identity(1,1),
time datetime not null
)

insert into yourtable ( time )
select convert(datetime, '20080101 12:46:00')
union all
select convert(datetime, '20080102 12:45:00')
union all
select convert(datetime, '20080103 15:16:09')
union all
select convert(datetime, '20080104 23:11:12')
union all
select convert(datetime, '20080105 20:19:08')
union all
select convert(datetime, '20080106 09:08:08')
union all
select convert(datetime, '20080107 10:09:09')
union all
select convert(datetime, '20080108 11:11:09')
GO

select t1.id,TimeToComplete=convert(varchar(15),DATEDIFF(hh,t3.time,t1.time))+ '' + substring(Convert(varchar(30),t3.time-t1.time,108),3,3) from yourtable t1
outer apply(select * from yourtable t2 where t1.id=t2.id+1)t3

drop table yourtable
[/code]

PBUH

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-13 : 02:40:40

Idera,

First of all thanks for giving sql stmt of table creation and data insertion.

I checked the output of the query and i felt that some of the results are incorrect.. ( i felt so because '20080101 12:46:00' - '20080102 12:45:00' should give 23 hours and 59 minutes).

I framed the below select.

Select T1.id, TimeToComplete = Convert(Varchar(5),datediff(mi,T2.time, T1.Time) / 60) +':' + Convert(Varchar(5), datediff(mi,T2.time, T1.Time) %60 )
from yourtable T1 inner join yourtable T2 on T1.id = t2.id+1

If time permits to you then please have a look.

Regards,
Bohra

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 03:01:48

Yes pk_bohra you are right.Mine one does give incorrect result.Thanks for the catch.


Select T1.id, TimeToComplete = Convert(Varchar(5),datediff(mi,T2.time, T1.Time) / 60) + ':' +Convert(Varchar(2), datediff(mi,T2.time, T1.Time) %60  )
from yourtable T1 left join yourtable T2 on T1.id = t2.id+1

I have changed your original query and have replaced inner join with left join so that the final resultset also includes the value of id 1.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources