help needed, get difference of datetime in 2 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

SQL Server Development (2000)

help needed, get difference of datetime in 2 rows

Author

Topic

naidu4u
Starting Member

23 Posts

Posted - 2010-11-01 : 19:25:03

I tried create a table with the data in it...
[code="sql"]create table mytable
(
changeid int,
refotsid int,
OTtime datetime,
Oldtype varchar(20),
Oldlocal varchar(50),
newtype varchar(20),
newlocal varchar(50),
DateChanged datetime,
DateClosed datetime
)
[/code]

inserting the rows in it...
[code="sql"]
insert into mytable (Changeid, refotsid, OTtime, Oldtype, Oldlocal, newtype, newlocal, DateChanged, DateClosed)

select '77397', '48242','10/29/2010 11:50:00 AM','Decision', '10/30/2010 6:00:00 AM', 'Parts', '16.00', '10/30/2010 9:08:00 AM', '10/31/2010 10:50:00 AM'
union all
select '77422', '48242', '10/29/2010 11:50:00 AM', 'Parts', '16.00', 'Decision', '10/30/2010 1:00:00 PM','10/30/2010 2:13:00 PM', '10/31/2010 10:50:00 AM'
union all
select '77432', '48242', '10/29/2010 11:50:00 AM', 'Decision', '10/30/2010 1:00:00 PM', 'Parts', '08.00', '10/30/2010 3:53:00 PM', '10/31/2010 10:50:00 AM'
union all
select '77470', '48242', '10/29/2010 11:50:00 AM', 'Parts', '08.00', 'Solid', '10/31/2010 6:00:00 AM', '10/30/2010 11:20:00 PM', '10/31/2010 10:50:00 AM'
union all
select '77352', '48226', '10/29/2010 3:00:00 AM', 'Decision', '10/29/2010 6:00:00 PM', 'Decision', '10/30/2010 4:00:00 AM', '10/29/2010 10:58:00 PM', '10/31/2010 4:20:00 PM'
union all
select '77394', '48226', '10/29/2010 3:00:00 AM', 'Decision', '10/30/2010 4:00:00 AM', 'Solid', '10/30/2010 2:00:00 PM', '10/30/2010 9:03:00 AM', '10/31/2010 4:20:00 PM'
union all
select '77444', '48226', '10/29/2010 3:00:00 AM', 'Solid', '10/30/2010 2:00:00 PM', 'Solid', '10/30/2010 8:00:00 PM', '10/30/2010 5:45:00 PM', '10/31/2010 4:20:00 PM'
union all
select '77472', '48226', '10/29/2010 3:00:00 AM', 'Solid', '10/30/2010 8:00:00 PM', 'Solid', '10/31/2010 4:00:00 AM', '10/31/2010 12:03:00 AM', '10/31/2010 4:20:00 PM'
union all
select '77488', '48226', '10/29/2010 3:00:00 AM', 'Solid', '10/31/2010 4:00:00 AM', 'Solid', '10/31/2010 10:00:00 AM', '10/31/2010 8:03:00 AM', '10/31/2010 4:20:00 PM'
[/code]

Now I need a new column XYZ in which
lets take for the same
refotsid = 48242 then for the first row I need XYZ as the dimediff(minute, DateChanged, OTtime)
where as from the second row I need XYZ as datediff(minute, DateChanged of prev row, DateChanged of present row)

and again for the next refotsid the same thing and so on...

hope this is clear and if not please let me know I will try something else....

Thanks in advance....

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-01 : 20:11:11

Define 1st row, second row, third row, ect...

Or are you looking for "pairs" of rows?

The order of rows in a database has no meaning...they are there own each Independent Entities

You need to establish some type of relationship

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

naidu4u
Starting Member

23 Posts

Posted - 2010-11-01 : 20:34:04

for any refotsid for the first row XYZ should be a datediff(minute, DateChanged, OTtime)
and for the remaining rows in the same refotsid, XYZ should be datediff(minute, DateChanged of prev row, DateChange of current row)

Thanks in advance....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources