how to calculate time duration within a table

Question

I have a table

   TABLE [dbo].[IssueStatus](
    [Id] [int] PRIMARY KEY ,
    [IssueId] [varchar(50)],
    [OldStatus] [int] ,
    [NewStatus] [int] ,
    [Updated] [datetime]

The 2 status columns can have 3 possible values 1, 2 and 3 (1 for open, 2 for in progress and 3 for resolved).
Updated 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.

Note:- The status may change from 1 to 3 directly.
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

I already checked - Calculate time duration between differents records in a table based on datetimes but it didnt help me much

The original situation is much more complicated 1 for open, 3 for in progress, 4 for reopen, 5 for resolved, 6 for closed

Thank You

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

Answer 1

The sql window function can really come in handy in situations as this. Assuming you use sql server 2012 or later, you can use the lead function.

For example: this lead usage adds the datetime of the 'next' record:

SELECT *, lead(updated,1,getdate()) over (partition by issueid order by updated) NextDate from issuestatus

"Next" in this case is of the same issueid (partition by) and in order of updated (with the default getdate() if there is no next record)

Since you have the next date in row, duration till the next row is simply nextdate - updated. (you could do the same with lag to get the duration from oldstatus to newstatus, but chose lead here because it can use the default getdate() for items currently in progress)

Lead can be used directly to calculate the duration:

SELECT IssueID, newstatus Status,
datediff(minute,updated, lead(updated,1,getdate()) over (partition by issueid order by updated)) DurationInMinutes 
from issuestatus

From there getting the totals can be easily done with a normal sum, making the final result:

select sum(DurationInMinutes) TotalDuration from
(
    SELECT IssueID, newstatus Status,
     datediff(minute,updated, lead(updated,1,getdate()) over (partition by issueid order by updated)) DurationInMinutes 
    from issuestatus
) d
where Status = 2 --only duration of status progress

Note that the where status= cannot be added to the subquery, otherwise lead would look to the next record where the status is also 2.

Of course, you can also do a group by IssueID to get the durations per issueid.

Answer 2

My query below shows for how long each entry was in status 2.

using this sample data:

Id  IssueId OldStatus NewStatus Updated        
1   aa      NULL        1       2015-01-01 14:00:00     
2   aa      1           2       2015-01-01 16:00:00     
4   aa      2           3       2015-01-01 17:30:00     
5   bb      NULL        1       2015-02-13 11:30:00     
6   bb      1           2       2015-02-13 12:56:00     
7   bb      2           3       2015-02-13 14:20:00     
8   cc      NULL        1       2015-02-14 11:30:00     
9   cc      1           2       2015-02-14 12:56:00     
10  cc      2           1       2015-02-14 13:19:00     
11  cc      1           2       2015-02-14 14:20:00     
12  cc      2           3       2015-02-14 14:25:00     

I can use this query:

 ;with NewStatus2 as (  
  SELECT Id, IssueId, Updated, ROW_NUMBER()  over (Order BY IssueId, id) PrevID FROM IssueStatus [is] 
  WHERE NewStatus = 2  )

, OldStatus2 as (  
  SELECT Id, IssueId, Updated, ROW_NUMBER()  over (Order BY IssueId, id) PrevID FROM IssueStatus [is] 
  WHERE OldStatus = 2  )

SELECT ns.IssueId
     , ns.Updated FromTime
     , os.Updated ToTime
     , DATEDIFF(minute , ns.Updated , os.Updated) as TimeSpan_Spent_In_Status_2
  FROM NewStatus2 ns
       INNER JOIN OldStatus2 os ON ns.IssueId = os.IssueId 
       AND ns.PrevID = os.PrevID                                 
  ORDER BY ns.Updated , os.Updated;

to get this result:

IssueId FromTime                ToTime            TimeSpan_Spent_In_Status_2
aa     2015-01-01 16:00:00  2015-01-01 17:30:00     90
bb     2015-02-13 12:56:00  2015-02-13 14:20:00     84
cc     2015-02-14 12:56:00  2015-02-14 13:19:00     23
cc     2015-02-14 14:20:00  2015-02-14 14:25:00     5

EDIT Made query and sample data match structure provided by @vibhavSarraf

Answer 3

This will give you the desired output:

SELECT IssueId, SUM(diff) +
      DATEDIFF(s, MIN(diffs.minU), MAX(maxU)) duration
FROM (
  SELECT i1.IssueId, (DATEDIFF(s, Updated, limits.maxU) * IIF(NewStatus=2,1,-1)) diff, minU, maxU
  FROM IssueStatus i1 INNER JOIN (
    SELECT i2.IssueId, MIN(UPDATED) minU, MAX(UPDATED) maxU
    FROM IssueStatus i2
    GROUP BY IssueId) limits ON
  i1.IssueId = limits.IssueId
  WHERE NewStatus < 3) diffs
GROUP BY IssueId

(SQLFiddle)

The idea here is that each row can be treated separately, calculating how much time is there from the Updated till the end of the issue (MAX(Updated)). Then you multiply by positive/negative 1, depending on status (positive for 2, negative for 1). If you sum all of these, you'll end up with the total progress time minus the initial open duration, so we add this back to the calculation. Unfortunately, the query turned "complicated" in terms of construction, built from 3 SELECTs. I believe it can be simplified (if I'll have the time later, I'll go over it again)