title: How to find the difference between two dates in same column?
body: <p>I have a table SO_STATUS that writes a record for each status change for a service order (we'll call the Service_Order_ID "Job_ID").  Job_ID references SERVICE_ORDER table.  When the service order is initialized, a record is written for that status type of "open" (StatusType 2) which shows the datetime.  Then another record is written in the status table for when it is "in progress" (StatusType 1).  And also when the service order is "closed", another record written in the status table (StatusType 3).  There are also other status types that may happen, but these are the most common.  The data in the SO_STATUS table looks like this:</p>
  
  <pre><code>id            Date                Job_ID  StatusTypeID  EmployeeID
  1     2012-01-01 09:05:00.000       51        2             5
  2     2012-01-01 10:00:00.000       52        2            12
  3     2012-01-01 10:01:00.000       51        1             5
  4     2012-01-01 12:15:00.000       53        2             8
  5     2012-01-01 12:16:00.000       51        3             5
  6     2012-01-01 13:00:00.000       52        1            12
  7     2012-01-01 14:00:00.000       52        3            12
  8     2012-01-01 14:15:00.000       53        1             8
  9     2012-01-01 15:00:00.000       54        2            11
  10    2012-01-01 16:30:00.000       53        3             8
  11    2012-01-01 15:00:00.000       54        1            11
  12    2012-01-01 16:30:00.000       54        3            11
  </code></pre>
  
  <p>I need to be able to find the time elapsed between each status change of each Job_ID.  Essentially, the duration of time spent from open to close for the job.</p>
  
  <p>Output would look something like (EmployeeName would be referenced from the EMPLOYEE table):</p>
  
  <pre><code>Job_ID       Duration    EmployeeName
    51         03:11:00        Kyle
    52         04:00:00        Chris
    53         04:15:00        Fred
    54         01:30:00        John
  </code></pre>
  
  <p>How would I go about getting this type of output?  Thank you.</p>