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.Act ioned)
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
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.Act
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.