Results 1 to 9 of 9
  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Sorry for the delay. I am traveling. Here is some test data
    Attached Files Attached Files

  5. #5
    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. #6
    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. #7
    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. #8
    Join Date
    Jun 2007
    Posts
    4
    I am so sorry for your inconvenience. Please try this:

    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. #9
    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
  •