[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Time Difference Between Rows

Posted on 2012-08-21
1
Medium Priority
?
328 Views
Last Modified: 2012-09-11
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
0
Comment
Question by:halifaxman
1 Comment
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38316368
Try this:
SELECT  r.IncidentId ,
        r.TimeIn ,
        isnull(slf.Actioned, r.TimeIn) as from_time ,
        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
        LEFT OUTER JOIN StateLog SLF ON sl.FromStateID = SLF.TostateID AND sl.jobID = SLF.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

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question