Link to home
Start Free TrialLog in
Avatar of Mark Wilson
Mark Wilson

asked on

Time Difference Between Rows

SQL 2008

I am using the query below

SELECT  r.IncidentId ,
        r.TimeIn ,
        sl.Actioned ,
        StateFrom.StateId FromStatedID ,
        StateFrom.Description FromStateDesc ,
        StateTo.StateId ToStateID ,
        StateTo.Description ToStateDesc ,
        e.NTUserName
FROM    Reporting r
        INNER JOIN StateLog SL ON r.jobID = SL.JobID
        INNER JOIN Editor e ON SL.EditorID = e.editorID
        LEFT OUTER JOIN Workflow w ON r.workflowID = w.workflowID
        INNER JOIN [State] AS StateTo ON sl.TostateID = StateTo.StateID
        INNER JOIN [State] AS StateFrom ON sl.FromStateID = StateFrom.StateID

ORDER BY r.IncidentId ,
        Actioned

An example of the data is attached

I want to calculate the time between each state in seconds

If the FromStateID = 1 and ToStateID = 2 the difference is staightforward DATEdiff(s,r.TimeIn,sl.Actioned)

After that is the difference in sl.actioned from one row to the next, using the example above

FromStateID = 2 to ToStateID = 3 then differnce between SL.Actioned i.e. 2008-04-18 20:12:08.760 to 2008-04-19 08:07:21.993

FromStateID = 3 to ToStateID = 17 then differnce between SL.Actioned i.e. 2008-04-19 08:07:21.993 to 2008-04-19 08:14:00.890

FromStateID = 17 to ToStateID = 10 then differnce between SL.Actioned i.e. 2008-04-19 08:14:00.890 to 2008-04-19 08:14:02.060

Any help would be appreciated
Example-Data.xls
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial