title: how to calculate time duration within a table
body: <p>I have a table</p>
  
  <pre><code>   TABLE [dbo].[IssueStatus](
      [Id] [int] PRIMARY KEY ,
      [IssueId] [varchar(50)],
      [OldStatus] [int] ,
      [NewStatus] [int] ,
      [Updated] [datetime]
  </code></pre>
  
  <p>The 2 status columns can have 3 possible values 1, 2 and 3 (1 for open, 2 for in progress and 3 for resolved).<br>
   <code>Updated</code> contains the datetime when the status is changed. The status of an issue is initially set to 1 automatically. I want to calculate total time(in seconds) when an issue was in progress.</p>
  
  <p>Note:- The status may change from 1 to 3 directly.<br>
  The status may change from 1 to 2 and then back to 1 and so on but the final status is guaranteed to be 3</p>
  
  <p>I already checked - <a href="https://stackoverflow.com/questions/10980922/calculate-time-duration-between-differents-records-in-a-table-based-on-datetimes">Calculate time duration between differents records in a table based on datetimes</a> but it didnt help me much</p>
  
  <p>The original situation is much more complicated
  1 for open, 3 for in progress, 4 for reopen, 5 for resolved, 6 for closed </p>
  
  <p>Thank You</p>
  
  <pre><code>79890   26327   3   In Progress 5   Resolved    2014-12-17 09:10:03.767
  74980   26328   3   In Progress 5   Resolved    2014-11-20 10:21:29.780
  74748   26328   1   Open    3   In Progress 2014-11-20 02:34:15.440
  77843   26329   1   Open    3   In Progress 2014-12-08 08:04:04.567
  77857   26329   1   Open    5   Resolved    2014-12-08 08:23:57.720
  77856   26329   3   In Progress 1   Open    2014-12-08 08:23:46.067
  75107   26330   1   Open    5   Resolved    2014-11-21 06:37:28.810
  76441   26330   5   Resolved    6   Closed  2014-12-02 07:27:39.927
  78638   26331   1   Open    3   In Progress 2014-12-10 07:47:41.347
  78091   26331   3   In Progress 1   Open    2014-12-09 02:44:36.970
  77858   26331   1   Open    3   In Progress 2014-12-08 08:28:08.597
  78641   26331   3   In Progress 1   Open    2014-12-10 07:57:03.603
  78642   26331   1   Open    5   Resolved    2014-12-10 07:57:11.483
  74753   26332   1   Open    3   In Progress 2014-11-20 02:59:11.013
  74763   26332   3   In Progress 5   Resolved    2014-11-20 03:04:01.127
  76846   26333   1   Open    5   Resolved    2014-12-05 00:57:09.140
  76849   26340   1   Open    5   Resolved    2014-12-05 01:52:05.957
  87861   26341   5   Resolved    6   Closed  2015-02-02 04:18:25.230
  85491   26341   1   Open    5   Resolved    2015-01-22 04:48:13.003
  77321   26342   3   In Progress 1   Open    2014-12-08 00:56:26.233
  75029   26342   1   Open    3   In Progress 2014-11-21 02:48:41.440
  79030   26342   3   In Progress 5   Resolved    2014-12-11 21:43:23.657
  76395   26342   1   Open    3   In Progress 2014-12-02 02:58:17.063
  75197   26342   3   In Progress 1   Open    2014-11-24 02:06:38.490
  78502   26342   1   Open    3   In Progress 2014-12-10 02:28:18.570
  74933   26343   1   Open    5   Resolved    2014-11-20 08:08:44.423
  74821   26344   1   Open    5   Resolved    2014-11-20 05:56:00.513
  75295   26345   1   Open    5   Resolved    2014-11-25 02:06:07.260
  </code></pre>