SQL Server - Duration between similar events for each Visit

Question

I am trying to calculate the time difference between events (in seconds) on a kiosk machine. Each time a person begins using the screen it creates a VisitID. Within that visit, a series of computer events are created that determine the beginning of the screen and the ending of the screen I have identified each event for each screen. Depending on what choices the user makes, they may or may not go through each event or screen. My data looks like this:

VisitID       ScreenTime           EventName
1              13:24:08         WelcomeScreen_Begin
1              13:24:15         Welcome_End
1              13:24:15         NameScreenInit_Start
1              13:24:32         Name_Screen_Terminate
1              13:24:33         CompanyScreen_Enter
1              13:24:45         Company_Lookup
1              13:24:45         Signoff_Now
1              13:24:52         SignOutEnd
2              13:45:02         WelcomeScreen_Begin
2              13:45:15         Welcome_End
2              13:45:15         NameScreenInit_Start
2              13:45:40         Name_Screen_Terminate
2              13:45:40         Signoff_Now
2              13:45:58         SignOutEnd

I would like for my results to look like this:

VisitID       WelcomeScreen       NameScreen      CompanyScreen    SignoffScreen
1               00:00:7           00:00:17           00:00:12          00:00:07
2               00:00:13          00:00:25              NULL           00:00:18

Ultimately, I would like to calculate the average time spent on EACH screen:

WelcomeScreenAVG      NameScreenAVG      CompanyScreenAVG      SignoffScreenAVG
00:00:10              00:00:21           00:00:12              00:00:13

I have been able to use subscripts to grab each timestamp within a DATEDIFF clause, but it ONLY works if I include the VisitID in the WHERE clause. I have hundreds of thousands of entries so that isn't the way to do it. I've been racking my brain on this for days. Does anyone have a solution, or at least know what I should be looking to do instead? Thanks in advance!

There are a few occurrences when the only event available to signify the beginning of one screen is the same event that ends the prior screen. For Example:

VisitID       ScreenTime       EventName
34            08:34:36         Delivery_summaryConfirm
34            08:34:47         Seal_questionsBegin
34            08:35:17         EndSeal_Inquiry

^^This is 2 Screens^^ In the example above, While Seal_questionsBegin is the Beginning of the SealInquiry Screen, it is the only event able to signify the end of the DeliverySummary Screen. This is because the DeliverySummary Screen is always followed by a SealInquiry Screen.

Answer 1

Split your dataset into "begins" and "ends", then do a simple join and a group by:

SELECT 
    Begins.ScreenName,
    AVG(DateDiff(second, Begins.ScreenTime, Ends.ScreenTime)) AS TimeSpentSeconds
FROM (
    SELECT 
        VisitID, 
        LEFT(EventName, LEN(EventName)-5) AS ScreenName, 
        ScreenTime 
    FROM MyTable WHERE EventName LIKE '%Begin'
) AS Begins
INNER JOIN (
    SELECT 
        VisitID, 
        LEFT(EventName, LEN(EventName)-3) AS ScreenName, 
        ScreenTime 
    FROM MyTable WHERE EventName LIKE '%End'
) AS Ends
ON Begins.VisitID = Ends.VisitID AND Begins.ScreenName = Ends.ScreenName
GROUP BY Begins.ScreenName

This returns the data in a row-wise fashion:

ScreenName      TimeSpentSeconds
----------      ----------------
WelcomeScreen   10
NameScreen      21
CompanyScreen   12
SignoffScreen   13

Use PIVOT to change this into a column-wise layout. To change the seconds into hours-minutes-seconds, see for example this post.

Answer 2

UPDATE

If you have a fixed event list for each screen you can do the below. I think it looks easier to read and is simpler than other posts...but that's me.

New Sql Fiddle

With tblUserTimes as (
  Select VisitId, 

  DateDiff(second, 
  Max(Case When EventName = 'WelcomeScreen_Begin' Then ScreenTime End),
  Max(Case When EventName = 'Welcome_End' Then ScreenTime End)) as WelcomeScreen,

  DateDiff(second, 
  Max(Case When EventName = 'NameScreenInit_Start' Then ScreenTime End),
  Max(Case When EventName = 'Name_Screen_Terminate' Then ScreenTime End)) as NameScreen,

  DateDiff(second, 
  Max(Case When EventName = 'CompanyScreen_Enter' Then ScreenTime End),
  Max(Case When EventName = 'Company_Lookup' Then ScreenTime End)) as CompanyScreen,

  DateDiff(second, 
  Max(Case When EventName = 'Signoff_Now' Then ScreenTime End),
  Max(Case When EventName = 'SignOutEnd' Then ScreenTime End)) as SignoffScreen

  from Visits
  Group By VisitId)

Select AVG(WelcomeScreen) as WelcomeScreenAvg,
       AVG(NameScreen) as NameScreenAvg,
       AVG(CompanyScreen) as CompanyScreenAvg,
       AVG(SignoffScreen) as SignoffScreenAvg
from tblUserTimes

---

See this SQL Fiddle for example with pivot.

Answer 3

The first think you need is to normalize the event name, the query take care of that, if can you should normalize the data or at least create the a translate table in you DB

Edit
The events are more than what I thought initially, the translate should be created as a full fledged table.

We can take advantage of the Translate table, adding the IsBegin and IsEnd bit fields and mapping the NormName field to the screen name, in addition to the dictionary, result in a simplified logic in the Times CTE.

CREATE TABLE NormalizeEvents (
  RealName VARCHAR(50)
, NormName VARCHAR(50)
, IsBegin BIT
, IsEnd BIT
)

INSERT INTO NormalizeEvents (RealName, NormName, IsBegin, IsEnd)
VALUES ('WelcomeScreen_Begin', 'WelcomeScreen', 1, 0)
     , ('Welcome_End', 'WelcomeScreen', 0, 1)
     , ('NameScreenInit_Start', 'NameScreen', 1, 0)
     , ('Name_Screen_Terminate', 'NameScreen', 0, 1)
     , ('CompanyScreen_Enter', 'CompanyScreen', 1, 0)
     , ('Company_Lookup', 'CompanyScreen', 0, 1)
     , ('Signoff_Now', 'SignoffScreen', 1, 0)
     , ('SignOutEnd', 'SignoffScreen', 0, 1)
     , ('Delivery_summaryConfirm', 'DeliverySummary', 1, 0)
     , ('Seal_questionsBegin', 'DeliverySummary', 0, 1)
     , ('Seal_questionsBegin', 'SealQuestions', 1, 0)
     , ('EndSeal_Inquiry', 'SealQuestions', 0, 1)

In case of an event name that is both the end of an event and the begin of a new one it should be inserted twice, as 'Seal_questionsBegin' in the example

WITH Times AS (
  SELECT VisitID, EventName = t.NormName
       , ScreenTimeBegin = Max(CASE t.IsBegin
                                    WHEN 1 THEN ScreenTime
                                    ELSE NULL
                               END)
       , ScreenTimeEnd = Max(CASE t.IsEnd
                                  WHEN 1 THEN ScreenTime
                                  ELSE NULL
                             END)
  FROM   Events e
         INNER JOIN NormalizeEvents t ON e.EventName = t.RealName
  GROUP BY VisitID, NormName
)
SELECT VisitID
     , [WelcomeScreen] = CAST(DATEADD(ss, WelcomeScreen, '1900-01-01') as Time)
     , [CompanyScreen] = CAST(DATEADD(ss, CompanyScreen, '1900-01-01') as Time)
     , [NameScreen] = CAST(DATEADD(ss, NameScreen, '1900-01-01') as Time)
     , [SignoffScreen] = CAST(DATEADD(ss, SignoffScreen, '1900-01-01') as Time)
     , [DeliverySummary]=CAST(DATEADD(ss, DeliverySummary, '1900-01-01') as Time)
     , [SealQuestions] = CAST(DATEADD(ss, SealQuestions, '1900-01-01') as Time)
FROM   (SELECT VisitID, EventName
             , Duration = DateDiff(ss, ScreenTimeBegin, ScreenTimeEnd)
        FROM   Times) SOURCE
       PIVOT (AVG(Duration) FOR EventName IN
              ([WelcomeScreen], [CompanyScreen], [NameScreen], [SignoffScreen]
             , [DeliverySummary], [SealQuestions])
             ) pvt

SQLFiddle demo.

To get the global average per screen just remove the VisitID field in the Source of the PIVOT and the main query.

WITH Times AS (
  SELECT VisitID, EventName = t.NormName
       , ScreenTimeBegin = Max(CASE t.IsBegin
                                    WHEN 1 THEN ScreenTime
                                    ELSE NULL
                               END)
       , ScreenTimeEnd = Max(CASE t.IsEnd
                                  WHEN 1 THEN ScreenTime
                                  ELSE NULL
                             END)
  FROM   Events e
         INNER JOIN NormalizeEvents t ON e.EventName = t.RealName
  GROUP BY VisitID, NormName
)
SELECT [WelcomeScreen] = CAST(DATEADD(ss, WelcomeScreen, '1900-01-01') as Time)
     , [CompanyScreen] = CAST(DATEADD(ss, CompanyScreen, '1900-01-01') as Time)
     , [NameScreen] = CAST(DATEADD(ss, NameScreen, '1900-01-01') as Time)
     , [SignoffScreen] = CAST(DATEADD(ss, SignoffScreen, '1900-01-01') as Time)
     , [DeliverySummary]=CAST(DATEADD(ss, DeliverySummary, '1900-01-01') as Time)
     , [SealQuestions] = CAST(DATEADD(ss, SealQuestions, '1900-01-01') as Time)
FROM   (SELECT EventName
             , Duration = DateDiff(ss, ScreenTimeBegin, ScreenTimeEnd)
        FROM   Times) SOURCE
       PIVOT (AVG(Duration) FOR EventName IN
              ([WelcomeScreen], [CompanyScreen], [NameScreen], [SignoffScreen]
             , [DeliverySummary], [SealQuestions])
             ) pvt

SQLFiddle demo (out of date for the data).

As the version of SQLServer is not specified in the question I assume is SQLServer 2012 or better.