I have a table that looks like this:
EventDateTime EventName AppID
-----------------------------------------
2014-06-27 22:17:19 GotFocus 1000
2014-06-27 22:17:20 LostFocus 1000
2014-06-27 22:17:22 GotFocus 1005
2014-06-27 22:17:24 LostFocus 1005
2014-06-27 22:17:27 GotFocus 1000
2014-06-27 22:17:30 LostFocus 1000
2014-06-27 22:17:37 GotFocus 1005
2014-06-27 22:17:40 LostFocus 1005
What I need to do is to create a T-SQL script that computes the total duration that each app was in focus (i.e. sum of all LostFocus-GotFocus per app). So for the table above I should get:
Duration AppID
------------------
4 1000
5 1005
How can I achieve this?
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 ORDER BY EventDateTime) AS RID,
EventDateTime
FROM Table1
WHERE EventName = 'GotFocus'
),
cteLostFocus
AS
(
SELECT AppID,
ROW_NUMBER() OVER(PARTITION BY AppID ORDER BY 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
GROUP BY gf.AppID
ORDER BY gf.AppID;
EDIT: Just condensing down some of the unnecessary CTEs. Fiddle link updated.
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
from Event g
join Event l on g.AppId = l.AppId and g.EventName = 'GotFocus' and l.EventName = 'LostFocus'
where l.EventDateTime = (select MIN(eventDateTime) from Event e3 where e3.AppId = g.AppId and e3.EventName = 'LostFocus' and e3.EventDateTime > g.EventDateTime)
group by g.AppId
order by g.AppId
SQL Fiddle
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(partition by appid order by eventdatetime) + 1) / 2 as 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.
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'
group by a,eventname
) as jj
join
(
select a , eventname ,sum(c) as bb
from #tem1
where eventname = 'logoff'
group by a,eventname
) as kk
on jj.a = kk.a
