Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
DateTimeDiff on different rows

Answered DateTimeDiff on different rows

  • Wednesday, December 19, 2012 12:50 AM
     
     
    Sign In to Vote
    0

    I need to calculate and display the time spent on an activity from the time entered, until the time entered on the next row.

    DateTime, Activity, Description, TimeSpent

    1/1/2012 00:50:01.3, status,task1,

    1/1/2012 00:54:31.2, status,break,

    1/1/2012 00:59:12.4, status,task2,

    I know how to calculate DateDiff between times on the same row, but need help to figure this one out. All help greatly appreciated.

    Rick

     

All Replies

  • Wednesday, December 19, 2012 2:07 AM
     
     
    Sign In to Vote
    0

    Take a look at this - Calculate Time Difference Between Two Rows SQL Server

    Hope this helps!

    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

     
  • Wednesday, December 19, 2012 11:06 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Try the below one second result set is your expected output in Minutes.

    Declare @tab as table(Tim  Datetime,Stat nvarchar(10), task nvarchar(6))
Insert into @tab values('1/1/2012 00:50:01.3', 'status','task1') 
Insert into @tab values('1/1/2012 00:54:31.2', 'status','break')
Insert into @tab values('1/1/2012 00:59:12.4', 'status','task2')
 
Select * from @tab
--------------------------------------------------------------------
WIth CTE as (
Select ROW_NUMBER() over(order by Tim) as ID,* from @tab)
Select Stat,task,DATEDIFF(Minute,(Select t2.Tim from CTE T2 where t2.ID = T1.ID-1),t1.Tim) as SpendTime from CTE T1 
------------------------------------------

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.