locked
ASA Query pattern to extract duration between events RRS feed

  • Question

  • Hi. I have the following Input schema:

    SessionID

    From

    To

    Timestamp

    1

    Main

    Enter.Module1

    t1

    1

    Module1

    Close

    t2

    2

    Main

    Enter.Module2

    t3

    3

    Main

    Enter.Module3

    t4

    3

    Module3

    Close

    t5

    Note that in SessionID 2 Module2 is Entered, but not Closed. This event is to be ignored. Timestamps are in ISO8601 format.

    I need a Query that produces the following Output schema:

    ModuleID

    TimestampEnter

    Duration

    Module1

    t1

    t2-t1

    Module3

    t4

    t5-t4

    Any ideas?

    Thanks. This is particularly helpful to a project working to support the most disadvantaged Australians.


    Alex Thomas | Principle Corporate Reporting Toolset Administrator Report Development Team | Software Development Unit | Information Management & Technology Department of Human Services | Level 39, Casselden Place, 2 Lonsdale Street, Melbourne, 3000 p. (03) 9096 0687 | f. (03) 9096 9107 | m. 0417 490 594 | e. alex.thomas@dhs.vic.gov.au

    Wednesday, October 28, 2015 2:25 AM

Answers

  • You can use JOIN to compute duration between two different type of events coming in the same stream.

    Something like the query below. Please note that you need to specify the maximum allowed time difference between events - I assumed one hour (or 3600 seconds):

    SELECT closeEvent.SessionID, DATEDIFF(second, openEvent.Timestamp, closeEvent.Timestamp)
    FROM
    (
       SELECT * FROM input TIMESTAMP BY [Timestamp]
       WHERE PATINDEX('Enter.%', [To]) > 0
    ) AS openEvent
    JOIN (
       SELECT *
       FROM input TIMESTAMP BY [Timestamp]
       WHERE [To] = 'Close'
    ) AS closeEvent
    ON openEvent.SessionID = closeEvent.SessionID
       AND DATEDIFF(second, openEvent, closeEvent) BETWEEN 0 AND 3600


    Tuesday, November 17, 2015 5:10 AM