determine time lapse between 2 rows

I have a table of machine data that captures fault codes, time machine stopped and time machine started. I can easily calculate the downtime, but how do i take the last start time and subtract it from the next stop time (1 row from the next row) to get an up time. I need it to group by short date and fault as I intend to use Excel as a reporting tool and pulling in all data will not fit. What is the most efficient way?
Thanks,
Leei guess you could do it..... but it will be easier for us to explain if you could post some default table values and table structures...|||well u will have to make a self-join for this to happen. something like below

select top 1 from [same table] where [time machine stopped] > outer.[time machine started] and MachineCode=outer.MachineCode etc.. etc...|||Sorry for the delay. I am traveling. Here is some test data|||I tried the self join and did get a return for 1 row. However, I think that I did a poor job of stating my goal. I will try and clairfy what I am trying to accomplish. For a given date range I need to first return the difference in the DWNTIMESTAMP_VAL0 and UPTIMESTAMP_VALO for that given row (this gives me the outage time). I also need to take the DWNTIMESTAMP_VAL0 for the very next row and find the difference from the UPTIMESTAMP_VAL0 the previous row (this gives me the uptime between the two downtime events). I will need this for each row in the date range. If the math part makes this more difficult, I can get the math done a different way if I could return a RS with row 1's DWNTIMESTAMP_VAL0, UPTIMESTAMP_VAL0 and row 2's DWNTIMESTAMP_VAL0 for a new row 1, row 2's DWNTIMESTAMP_VAL0, UPTIMESTAMP_VAL0 and row 3's DWNTIMESTAMP_VAL0 for a new row 2, ... Is this at all possible to do within SQL?|||Hope following procedure will help to solve your problem:

Create Procedure sp_getNextUpTiming
As
Begin

Select * Into #TempDOWNTIMELOG From [DOWNTIMELOG]

Alter Table #TempDOWNTIMELOG Add RowId INT Identity(1, 1)

Select
DATEDIFF(SS, CONVERT(DATETIME, D1D, 101) , CONVERT(DATETIME, D1U, 101)) As DownTime,
DATEDIFF(SS, CONVERT(DATETIME, D1U, 101) , CONVERT(DATETIME, D2D, 101)) As NextUpTime
From
(
Select
D1.[DWNTIMESTAMP_VAL0] As D1D, D1.[UPTIMESTAMP_VAL0] As D1U,
D2.[DWNTIMESTAMP_VAL0] As D2D, D2.[UPTIMESTAMP_VAL0] As D2U
From #TempDOWNTIMELOG As D1
Inner Join #TempDOWNTIMELOG As D2 On D1.RowId In
(Select Max(RowId) From #TempDOWNTIMELOG Where RowId<D2.RowId)
) As tblTimings

End

Prajkta A.
------
Software Engineer
Clarion Technologies
(SEI CMMI Level 3)
Pune, India
www. clariontechnologies. co. in|||When I call the procedure, it fails with Invaild Column Name "RowId"|||I am so sorry for your inconvenience. Please try this:

Alter Procedure sp_getNextUpTiming
As
Begin

Select * Into #TempDOWNTIMELOG From [DOWNTIMELOG]

Alter Table #TempDOWNTIMELOG Add RowId INT Identity(1, 1)

DECLARE @.strTest vARCHAR(4000)
set @.strTest = 'Select
DATEDIFF(SS, CONVERT(DATETIME, D1D, 101) , CONVERT(DATETIME, D1U, 101)) As DownTime,
DATEDIFF(SS, CONVERT(DATETIME, D1U, 101) , CONVERT(DATETIME, D2D, 101)) As NextUpTime
From
(
Select
D1.[DWNTIMESTAMP_VAL0] As D1D, D1.[UPTIMESTAMP_VAL0] As D1U,
D2.[DWNTIMESTAMP_VAL0] As D2D, D2.[UPTIMESTAMP_VAL0] As D2U
From #TempDOWNTIMELOG As D1
Inner Join #TempDOWNTIMELOG As D2 On D1.RowId In
(Select Max(RowId) From #TempDOWNTIMELOG Where RowId<D2.RowId)
) As tblTimings'

EXEC(@.strTest)

End|||thanks so much!! able to return the uptime and downtime. i will now attempt to gain enough understanding so that i can do group by as there are different pieces of equipment and i need to make certain that i pull the data by line.