Results 1 to 3 of 3

Thread: Query help

  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Query help

    I have a table with user activity and need to display only the start and end timestamp of the activity. I don't know how can we write an logic for this please help me in a bettr way to work on it

    Below is the sample data :-
    User Activity_log
    --------------------------------------------------- -----------------------
    Auto_Generated 2014-08-17 15:25:03.333
    Auto_Generated 2014-08-17 15:25:03.333
    Auto_Generated 2014-08-17 15:25:03.348
    Auto_Generated 2014-08-17 15:25:03.348
    Auto_Generated 2014-08-17 15:25:03.365
    Auto_Generated 2014-08-17 15:25:03.365
    Auto_Generated 2014-08-17 15:25:03.379
    Auto_Generated 2014-08-17 15:25:03.379
    Jack 2014-08-17 15:25:50.273
    Jack 2014-08-17 15:25:50.313
    Jack 2014-08-17 15:25:54.433
    Jack 2014-08-17 15:25:54.91
    Jack 2014-08-17 15:25:54.922
    Jack 2014-08-17 15:25:54.938
    Jack 2014-08-17 15:25:54.952
    Jack 2014-08-17 15:25:54.982
    Jack 2014-08-17 15:25:55.022
    Jack 2014-08-17 15:26:02.26
    Jack 2014-08-17 15:26:02.28
    Kate 2014-08-19 11:12:31.206
    Kate 2014-08-19 11:12:31.246
    Kate 2014-08-19 11:12:31.337
    Kate 2014-08-19 11:12:31.386
    Kate 2014-08-19 11:12:31.446
    Kate 2014-08-19 11:43:43.795
    Kate 2014-08-19 11:43:59.876
    Kate 2014-08-19 11:43:59.888
    Kate 2014-08-19 11:43:59.916
    Kate 2014-08-19 11:43:59.941
    Tom 2014-08-19 12:56:31.306
    Tom 2014-08-19 12:56:37.892
    Tom 2014-08-19 12:56:38.779
    Tom 2014-08-19 12:56:38.798
    Tom 2014-08-19 12:56:38.82
    System 2014-08-24 07:00:35.574
    System 2014-08-24 07:00:35.577
    System 2014-08-24 07:00:35.578
    System 2014-08-24 07:00:35.587
    System 2014-08-24 07:00:35.595
    System 2014-08-24 07:00:35.618
    Kate 2014-12-23 08:22:17.949
    Kate 2014-12-23 08:22:17.958
    Kate 2014-12-23 08:22:17.989
    Kate 2014-12-23 08:22:18.013
    Rita 2014-08-24 11:06:38.593
    Rita 2014-08-24 11:06:44.816
    Rita 2014-08-24 11:06:44.848
    Rita 2014-08-24 11:06:44.862
    Rita 2014-08-24 11:06:44.888
    Qutput :-
    Jack 2014-08-17 15:25:50.273 2014-08-17 15:26:02.28
    Kate 2014-08-19 11:12:31.206 2014-08-19 11:43:59.941
    Tom 2014-08-19 12:56:31.306 2014-08-19 12:56:38.82
    Kate 2014-12-23 08:22:17.949 2014-12-23 08:22:18.013
    Rita 2014-08-24 11:06:38.593 2014-08-24 11:06:44.888

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Your example is way too simplified to be useful. How do you know that there were two activities for Kate, and not one long activity?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by lazydev View Post
    I have a table with user activity and need to display only the start and end timestamp of the activity. I don't know how can we write an logic for this please help me in a bettr way to work on it

    Below is the sample data :-

    Qutput :-
    I assume that you want to "merge" activites when there is no other user interfering. This kind of problem is sometime referred to as "island and gap" problems. Using window functions there is a trivial solution:

    Code:
    select x, user, min(activity_log), max(activity_log)
    from (
        select user, activity_log
                , row_number() over (order by activity_log) 
                - row_number() over (partition by user order by activity_log) as x
        from T
    ) group by x, user
    The idea is to enumerate all activities and enumerate the activities per user. If the difference between those numbers changes it means that someone else interfered. Removing x from the answer is left as an exercise ;-)
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •