CODEWITHSUNDEEP
sqlsql-server-2008
Oshkosh
Oshkosh

Reputation: 13

Calculate total time spent by group and one datetime column

I have a workflow application where the workflow is written to the DB as shown below when the status changes. There is no end time as it is a sequence of events. I want to create a query that will group by the WorkFlowID and total the amount of time spent in each. I am not sure how to even begin

My table and data looks like this

+------------+---------------------+
| WorkFlowID |      EventTime      |
+------------+---------------------+
|          1 | 07/15/2015 12:00 AM |
|          2 | 07/15/2015 12:10 AM |
|          3 | 07/15/2015 12:20 AM |
|          2 | 07/15/2015 12:30 AM |
|          3 | 07/15/2015 12:40 AM |
|          4 | 07/15/2015 12:50 AM |
+------------+---------------------+

My end result should be like:

+------------+-----------------+
| WorkFlowID | TotalTimeInMins |
+------------+-----------------+
|          1 |              10 |
|          2 |              20 |
|          3 |              20 |
|          4 |              10 |
+------------+-----------------+

Upvotes: 0

Views: 1203

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40359

The basis of a method that works in all (ok, I don't know about SQL 6.5) editions is to use the group by clause:

SELECT
   WorkFlowID
  ,datediff(mi, min(EventTime), max(EventTime))  TotalTimeInMins
 from MyTable
 group by WorkFlowID

This does indeed leave the question of how you got 10 minutes with a start time and (presumably) no end time. As written, this query would list the WorkFlowID with TotalTimeInMins = 0, which seems accurate enough. The following variant would remove all "start-only" items:

SELECT
   WorkFlowID
  ,datediff(mi, min(EventTime), max(EventTime))  TotalTimeInMins
 from MyTable
 group by WorkFlowID
 having count(*) > 1

(The quick explanation: having is to group by as where is to from)

Upvotes: 0

shA.t
shA.t

Reputation: 16968

As an alternative:

;WITH t AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY EventTime) As rn
    FROM
        yourTable)
SELECT
    t1.WorkFlowID,
    SUM(DATEDIFF(SECOND, t1.EventTime, ISNULL(t2.EventTime, GETDATE()) / 60)  As TotalTimeInMins
FROM t t1
  LEFT JOIN t t2 
    ON t1.rn = t2.rn - 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270493

In SQL Server 2012+, you would just use lead(). There are several ways to approach this in SQL Server 2008. Here is one using `outer apply:

select t.WorkFlowId,
       sum(datediff(second, EventTime, nextTime)) / 60.0 as NumMinutes
from (select t.*, t2.EventTime as nextTime
      from table t outer apply
           (select top 1 t2.*
            from table t2
            where t2.EventTime > t.EventTime
            order by t2.EventTime
           ) t2
     ) tt
group by t.WorkFlowId;

The only question is how you get "10" for event 4. There is no following event, so that value doesn't make sense. You can use datediff(second, EventTime coalesce(NextEvent, getdate()) to handle the NULL value.

Upvotes: 1

Related Questions