# Thread: determine time lapse between 2 rows

1. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123

## Unanswered: 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,
Lee

2. Registered User
Join Date
May 2007
Location
somewhere in dbforums
Posts
221
i 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...

3. Registered User
Join Date
Feb 2003
Location
India
Posts
216
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...

4. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
Sorry for the delay. I am traveling. Here is some test data

5. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
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?

6. Registered User
Join Date
Jun 2007
Posts
4
Hope following procedure will help to solve your problem:

HTML Code:
```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

7. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
When I call the procedure, it fails with Invaild Column Name "RowId"

8. Registered User
Join Date
Jun 2007
Posts
4

HTML Code:
```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```

9. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•