T-sql Script To Calculate Time Difference Between Multiple Rows

I have a table that looks like this: EventDateTime EventName AppID ----------------------------------------- 2014-06-27 22:17:19 GotFocus 1000 2014-06-27 22:17:2

Solution 1:

As xQbert pointed out, depends on how clean your data is. Assuming that there is one LostFocus for every GotFocus and that LostFocus EventDateTime is greater than or equal to GotFocus EventDateTime, this should work (fiddle: http://sqlfiddle.com/#!3/f36a4/14):

WITH cteGotFocus
AS
(
  SELECT  AppID,
          ROW_NUMBER() OVER(PARTITION BY AppID ORDERBY EventDateTime) AS RID,
          EventDateTime
  FROM    Table1
  WHERE   EventName = 'GotFocus'
),
cteLostFocus
AS
(
  SELECT  AppID,
          ROW_NUMBER() OVER(PARTITION BY AppID ORDERBY EventDateTime) AS RID,
          EventDateTime
  FROM    Table1
  WHERE   EventName = 'LostFocus'
)

SELECT   SUM(DATEDIFF(s, gf.EventDateTime, lf.EventDateTime)) AS Duration,
         gf.AppID
FROM     cteGotFocus gf INNER JOIN cteLostFocus lf
         ON gf.AppID = lf.AppID AND
            gf.RID = lf.RID
GROUPBY  gf.AppID
ORDERBY  gf.AppID;

EDIT: Just condensing down some of the unnecessary CTEs. Fiddle link updated.

Solution 2:

I like VBlades' answer (I think it's easier to read), but this might perform a little better:

select g.AppId, sum(DATEDIFF(S, g.EventDateTime, l.EventDateTime)) Duration
fromEvent g
joinEvent l on g.AppId = l.AppId and g.EventName = 'GotFocus' and l.EventName = 'LostFocus'where l.EventDateTime = (select MIN(eventDateTime) fromEvent e3 where e3.AppId = g.AppId and e3.EventName = 'LostFocus' and e3.EventDateTime > g.EventDateTime)groupby g.AppId
orderby g.AppId

SQL Fiddle

Solution 3:

If the sequence of events is always GF, LF then you could use this query:

select y.appid, y.groupnum, datediff(second, y.gotfocus, y.lostfocus) seconds
from (( row_number() over(partitionby appid orderby eventdatetime) +1) /2as groupnum,
appid, eventname, eventdatetime
from dbo.mytable) x
pivot( max(x.eventdatetime) for x.eventname in ([gotfocus], [lostfocus]) y

Note: I've not tested this solution.

Solution 4:

I have taken the same scenario though with different data types and answer which is simple to read is below

select kk.bb - jj.aa from
(select a , eventname ,sum(c) as aa
    from #tem1
    where eventname = 'login'groupby a,eventname
) as jj

join 
(select a , eventname ,sum(c) as bb
    from #tem1
    where eventname = 'logoff'groupby a,eventname
) as kk
on jj.a = kk.a