This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 29, 2013 1:29 AM by Etbin RSS

SQl

7688438 Newbie
Currently Being Moderated
Hi Experts,

I have a table called tracking with track_id and start_time and action_type.When ever a customer login his action_type is 1 and his start_time is the login time.
trying to figure out the log out time and duration based on start_time and action_type.
action_type=1 means login
action_type=2 means on_track

desc tracking;

track_id number,
start_time systimestamp,
action_type number
;

Data in tracking table:
track_id start_time action_type
100 01-jan-11 00:01:01 1
100 01-jan-11 00:02:01 2
100 01-jan-11 00:05:01 2
100 01-jan-11 00:09:01 2
100 01-jan-11 00:10:01 2
100 01-jan-11 00:15:01 2

100 01-jan-11 00:18:01 1
100 01-jan-11 00:19:01 2
100 01-jan-11 00:20:01 2
100 01-jan-11 00:26:01 2
100 01-jan-11 00:28:01 2
100 01-jan-11 00:29:01 2

100 01-jan-11 00:32:01 1

Expected output:

track_id start_time end_time duration(in minutes)
100 01-jan-11 00:01:01 01-jan-11 00:15:01 14
100 01-jan-11 00:18:01 01-jan-11 00:29:01 11

Appreciate your help

Regards
K
.....
  • 1. Re: SQl
    rp0428 Guru
    Currently Being Moderated
    >
    I have a table called tracking with track_id and start_time and action_type.When ever a customer login his action_type is 1 and his start_time is the login time.
    trying to figure out the log out time and duration based on start_time and action_type.
    action_type=1 means login
    action_type=2 means on_track
    >
    I assume you can do the time calc yourself but this code (ugly at best) will give you the raw data you need; assuming your data is like you said
    with q as (select 100 track_id, to_date('01-jan-11 00:01:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:02:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:05:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:09:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:10:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:15:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:18:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:19:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:20:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:26:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:28:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:29:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual
      union all select 100 track_id, to_date('01-jan-11 00:32:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual
    ),
    q1 as (select track_id, start_time, action_type, lag (start_time, 1, null) over (partition by track_id order by start_time) end_time
    from q),
    q2 as (select track_id, start_time, end_time from q1 where action_type = 1)
    select track_id, start_time, lead(end_time, 1, start_time) 
        over (partition by track_id order by start_time) end_time from q2
    
    TRACK_ID     START_TIME     END_TIME
    100     1/1/2011 12:01:01 AM     1/1/2011 12:15:01 AM
    100     1/1/2011 12:18:01 AM     1/1/2011 12:29:01 AM
    100     1/1/2011 12:32:01 AM     1/1/2011 12:32:01 AM
    I left the last line in the result but you won't use it: just filter out where start_time = end_time. Then compute your elapsed time by subtracting the START_TIME from the END_TIME for each row. Your data asssumes the very last row for a track_id is a type '1'.

    You can see what each of the table expressions give you by replacing the last SELECT query with a query of the table expression
    select * from q   -- just produces your raw data
    
    select * from q1 -- uses LAG to get a rows END_TIME as the previous rows START_TIME
    
    select * from q2 -- filters out the type '2' rows
    
    TRACK_ID     START_TIME     END_TIME
    100     1/1/2011 12:01:01 AM     
    100     1/1/2011 12:18:01 AM     1/1/2011 12:15:01 AM
    100     1/1/2011 12:32:01 AM     1/1/2011 12:29:01 AM
    The actual final query uses the above data and the LEAD function to get a rows actual END_TIME from the following row. The last row has no following row so a DEFAULT of the rows START_TIME is used. That rows could be filtered out as part of the query but I left it in.
  • 2. Re: SQl
    Etbin Guru
    Currently Being Moderated
    Maybe NOT TESTED! use q from the post above
    q1 as
    (select track_id,
            start_time,
            action_type,
            row_number() over (partition by track_id order by start_time) rn
       from q
    )
    select s.track_id,
           s.start_time,
           e.start_time end_time,
           (e.start_time - s.start_time) * 24 * 60 duration_minutes
      from (select track_id,
                   start_time,
                   lead(rn,1) over (partition by track_id order by start_time) - 1 rn
              from q1
             where action_type = 1
           ) s,
           q1 e
     where e.track_id = s.track_id
       and e.rn = s.rn
    Regards

    Etbin
  • 3. Re: SQl
    €$ħ₪ Expert
    Currently Being Moderated
    Choose a data set just like below to compare the data in your table...and use BETWEEN to get the desired result set.
    SELECT 1 ID,TO_DATE ('01-jan-11 00:01:01', 'dd-mon-rr hh24:mi:ss')
                       start_time,
                    TO_DATE ('01-jan-11 00:18:01', 'dd-mon-rr hh24:mi:ss')
                       start_time2,
                       TO_DATE ('01-jan-11 00:29:01', 'dd-mon-rr hh24:mi:ss') start_time3
    from dual
  • 4. Re: SQl
    7688438 Newbie
    Currently Being Moderated
    Hi ,

    Thanks for your updates. My duration should be endtime-starttime
    Original data:

    Track_id start_time Action_type


    10010124397     01-MAY-11 06.02.04.000000000 PM     1-----------------------Starttime
    10010124397     01-MAY-11 06.02.15.000000000 PM     2
    10010124397     01-MAY-11 06.02.19.000000000 PM     2
    10010124397     01-MAY-11 06.02.25.000000000 PM     2
    10010124397     01-MAY-11 06.02.46.000000000 PM     2
    10010124397     01-MAY-11 06.03.31.000000000 PM     2
    10010124397     01-MAY-11 06.03.42.000000000 PM     2
    10010124397     01-MAY-11 06.03.47.000000000 PM     2
    10010124397     01-MAY-11 06.03.48.000000000 PM     2
    10010124397     01-MAY-11 06.03.53.000000000 PM     2
    10010124397     01-MAY-11 06.04.01.000000000 PM     2
    10010124397     01-MAY-11 06.04.18.000000000 PM     2
    10010124397     01-MAY-11 06.04.19.000000000 PM     2
    10010124397     01-MAY-11 06.04.27.000000000 PM     2
    10010124397     01-MAY-11 06.04.28.000000000 PM     2
    10010124397     01-MAY-11 06.04.29.000000000 PM     2
    10010124397     01-MAY-11 06.04.35.000000000 PM     2
    10010124397     01-MAY-11 06.08.09.000000000 PM     2
    10010124397     01-MAY-11 06.08.53.000000000 PM     2
    10010124397     01-MAY-11 06.18.11.000000000 PM     2
    10010124397     01-MAY-11 06.27.25.000000000 PM     2
    10010124397     01-MAY-11 06.28.13.000000000 PM     2
    10010124397     01-MAY-11 06.28.14.000000000 PM     2
    10010124397     01-MAY-11 06.28.16.000000000 PM     2
    10010124397     01-MAY-11 06.31.11.000000000 PM     2
    10010124397     01-MAY-11 06.31.12.000000000 PM     2
    10010124397     01-MAY-11 06.31.13.000000000 PM     2
    10010124397     01-MAY-11 06.31.16.000000000 PM     2
    10010124397     01-MAY-11 06.31.17.000000000 PM     2
    10010124397     01-MAY-11 06.31.19.000000000 PM     2
    10010124397     01-MAY-11 06.31.20.000000000 PM     2
    10010124397     01-MAY-11 06.31.21.000000000 PM     2
    10010124397     01-MAY-11 06.31.25.000000000 PM     2
    10010124397     01-MAY-11 06.32.12.000000000 PM     2
    10010124397     01-MAY-11 06.40.24.000000000 PM     2
    10010124397     01-MAY-11 06.41.15.000000000 PM     2
    10010124397     01-MAY-11 06.41.16.000000000 PM     2
    10010124397     01-MAY-11 06.41.18.000000000 PM     2
    10010124397     01-MAY-11 06.42.11.000000000 PM     2
    10010124397     01-MAY-11 06.42.12.000000000 PM     2
    10010124397     01-MAY-11 06.42.14.000000000 PM     2
    10010124397     01-MAY-11 07.45.36.000000000 PM     2-------------------------End time
    10010124397     01-MAY-11 07.45.37.000000000 PM     1---------------------next session start time
    10010124397     01-MAY-11 07.45.43.000000000 PM     2
    10010124397     01-MAY-11 07.45.46.000000000 PM     2
  • 5. Re: SQl
    rp0428 Guru
    Currently Being Moderated
    >
    Thanks for your updates. My duration should be endtime-starttime
    >
    The code I posted above produces the correct 'endtime' and 'starttime' for you. You just need to subtract them and then convert the value to the format that you want.

    What is the problem you are now having?
  • 6. Re: SQl
    7688438 Newbie
    Currently Being Moderated
    Hi ,

    Thanks for your input.Now i just want to add the new column(session_count(no of 2's between 1's)

    Expected output:

    track_id Start_time End_time minutes session_count(No of 2's between 1st login and next login)

    10010124397      01-MAY-11 18:02:04     01-MAY-11 19:45:36     104 20
    10010124397     01-MAY-11 19:45:37     01-MAY-11 20:15:42     30 6
    10010124397     02-MAY-11 15:27:23     02-MAY-11 16:00:20     33 7
    10010124397     02-MAY-11 17:19:16     02-MAY-11 17:36:32     17 5
    10010124397     03-MAY-11 22:41:39     03-MAY-11 23:47:00     65 13
    10010124397     04-MAY-11 12:24:33     04-MAY-11 12:30:42     6 2
    10010124397     04-MAY-11 15:44:11     04-MAY-11 15:59:43     16 4
    10010124397     04-MAY-11 17:47:06     04-MAY-11 18:27:22     40 8
    10010124397     04-MAY-11 23:32:57     04-MAY-11 23:50:11     17 5
    10010124397     05-MAY-11 23:14:14     05-MAY-11 23:33:47     20 6
    10010124397     06-MAY-11 12:03:56     06-MAY-11 12:07:07     3 2

    Thanks in advance

    K

    Edited by: 7688438 on Apr 23, 2013 11:58 AM

    Edited by: 7688438 on Apr 23, 2013 12:01 PM
  • 7. Re: SQl
    Etbin Guru
    Currently Being Moderated
    Maybe NOT TESTED!
    q1 as
    (select track_id,
            start_time,
            action_type,
            row_number() over (partition by track_id order by start_time) rn
       from q
    )
    select s.track_id,
           s.start_time,
           e.start_time end_time,
           (e.start_time - s.start_time) * 24 * 60 duration_minutes,
           nvl(s.rn_next,s.rn_max + 1) - s.rn - 1 session_count
      from (select track_id,
                   start_time,
                   rn,
                   lead(rn,1) over (partition by track_id order by start_time) rn_next,
                   (select count(*) from q1) rn_max
              from q1
             where action_type = 1
           ) s,
           q1 e
     where e.track_id = s.track_id
       and e.rn = s.rn_next - 1
    Regards

    Etbin
  • 8. Re: SQl
    7688438 Newbie
    Currently Being Moderated
    Hi Experts,

    No that query getting count of the entire table.



    Regards

    K

    Edited by: 7688438 on Apr 23, 2013 8:09 PM
  • 9. Re: SQl
    7688438 Newbie
    Currently Being Moderated
    Thanks for your input.Now i just want to add the new column(session_count(no of 2's between 1's)

    Expected output:

    track_id Start_time End_time minutes session_count(No of 2's between 1st login and next login)

    10010124397 01-MAY-11 18:02:04     01-MAY-11 19:45:36     104 20
    10010124397     01-MAY-11 19:45:37     01-MAY-11 20:15:42     30 6
    10010124397     02-MAY-11 15:27:23     02-MAY-11 16:00:20     33 7
    10010124397     02-MAY-11 17:19:16     02-MAY-11 17:36:32     17 5
    10010124397     03-MAY-11 22:41:39     03-MAY-11 23:47:00     65 13
    10010124397     04-MAY-11 12:24:33     04-MAY-11 12:30:42     6 2
    10010124397     04-MAY-11 15:44:11     04-MAY-11 15:59:43     16 4
    10010124397     04-MAY-11 17:47:06     04-MAY-11 18:27:22     40 8
    10010124397     04-MAY-11 23:32:57     04-MAY-11 23:50:11     17 5
    10010124397     05-MAY-11 23:14:14     05-MAY-11 23:33:47     20 6
    10010124397     06-MAY-11 12:03:56     06-MAY-11 12:07:07     3 2
    Thanks in advance

    K
  • 10. Re: SQl
    Etbin Guru
    Currently Being Moderated
    Like this ?
    with
    q as 
    (select 100 track_id, to_date('01-jan-11 00:01:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:02:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:05:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:09:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:10:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:15:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:18:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:19:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:20:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:26:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:27:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:28:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:29:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 100 track_id, to_date('01-jan-11 00:30:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 200 track_id, to_date('01-jan-11 00:31:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual union all
     select 300 track_id, to_date('01-jan-11 00:32:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual union all
     select 200 track_id, to_date('01-jan-11 00:35:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 200 track_id, to_date('01-jan-11 00:37:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 300 track_id, to_date('01-jan-11 00:37:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 300 track_id, to_date('01-jan-11 00:39:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 200 track_id, to_date('01-jan-11 00:39:01', 'dd-mon-rr hh24:mi:ss') start_time, 2 action_type from dual union all
     select 200 track_id, to_date('01-jan-11 00:40:01', 'dd-mon-rr hh24:mi:ss') start_time, 1 action_type from dual
    ),
    q1 as
    (select track_id,
            start_time,
            action_type,
            row_number() over (partition by track_id order by start_time) rn,
            count(*) over (partition by track_id order by start_time
                           rows between unbounded preceding and unbounded following
                          ) rn_max
       from q
    ),
    q2 as
    (select track_id,
            start_time,
            rn,
            lead(rn,1,rn_max + 1) over (partition by track_id order by start_time) rn_next,
            rn_max,
            nullif(lead(track_id,1,1) over (partition by track_id order by start_time),track_id) status
       from q1
      where action_type = 1
    )
    select s.track_id,
           s.start_time,
           e.start_time end_time,
           (e.start_time - s.start_time) * 24 * 60 duration_minutes,
           nvl(s.rn_next,s.rn_max + 1) - s.rn - 1 session_count,
           nvl2(s.status,decode((e.start_time - s.start_time),0,'logged in','on track'),'logged out') status
      from q2 s,
           q1 e
     where e.track_id = s.track_id
       and e.rn = s.rn_next - 1
    Regards

    Etbin
  • 11. Re: SQl_CNT
    7688438 Newbie
    Currently Being Moderated
    Hi Experts,

    Thanks for your inputs.

    I have a new requirement based on my data.My data says user logins and do some clicks(heart beats) and walked away.if the screen is idle for more than 75 minutes then we don't need to count those minutes.

    I have to find end_time,duration,HEAR_BEAT_CNT based on action_type_code and start_time and the threshold time(session idle time) is more than 75 minutes we don't need to find that duration.


    Input data:

    student_id start_time action_type_code

    10010124397     01-MAY-11 06.02.04.000000000 PM     1---SESSION LOGIN TIME
    10010124397     01-MAY-11 06.02.15.000000000 PM     2
    10010124397     01-MAY-11 06.02.19.000000000 PM     2
    10010124397     01-MAY-11 06.02.25.000000000 PM     2
    10010124397     01-MAY-11 06.02.46.000000000 PM     2
    10010124397     01-MAY-11 06.03.31.000000000 PM     2
    10010124397     01-MAY-11 06.03.42.000000000 PM     2
    10010124397     01-MAY-11 06.03.47.000000000 PM     2
    10010124397     01-MAY-11 06.03.48.000000000 PM     2
    10010124397     01-MAY-11 06.03.53.000000000 PM     2 <-(1ST SESSION_ENDS HERE)
    IF THE GAP B/W TWO HEART BEATS IS MORE THAN 75 MINUTES THEN
    10010124397     01-MAY-11 10.04.01.000000000 PM     2 <-(PRETEND THIS ACTION_TYPE AS RELOGIN(1))
    10010124397     01-MAY-11 10.04.18.000000000 PM     2
    10010124397     01-MAY-11 10.04.19.000000000 PM     2
    10010124397     01-MAY-11 10.04.27.000000000 PM     2 (2ND SESSION ENDS HERE)

    10010124397     01-MAY-11 10.06.04.000000000 PM     1 -- 3rd session login
    10010124397     01-MAY-11 10.06.29.000000000 PM     2
    10010124397     01-MAY-11 10.06.35.000000000 PM     2
    10010124397     01-MAY-11 10.08.09.000000000 PM     2
    10010124397     01-MAY-11 10.09.53.000000000 PM     2----3rd session ends here

    10010124397     01-MAY-11 10.18.11.000000000 PM     1



    Expected o/p:

    STUDENTID START_TIME ENDTIME DURATION_IN_MINUTES HEAR_BEAT_CNT(NO OF 2'S)

    10010124397 01-MAY-11 06.02.04.000000000 PM 01-MAY-11 06.03.53.000000000 PM 1.5 9
    10010124397     01-MAY-11 10.04.01.000000000 PM 01-MAY-11 10.04.27.000000000 PM 0.43 3
    10010124397     01-MAY-11 10.06.04.000000000 PM 01-MAY-11 10.09.53.000000000 PM 3.45 4

    thanks in advance
    K

    Edited by: 7688438 on Apr 25, 2013 6:17 PM
  • 12. Re: SQl_CNT
    Etbin Guru
    Currently Being Moderated
    with
    q as 
    (select 10010124397 student_id,
            to_timestamp('01-05-2011 06.02.04.000000000','DD-MM-YYYY HH24.MI.SS.FF') start_time,
            1 action_type
       from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.02.15.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.02.19.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.02.25.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.02.46.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.03.31.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.03.42.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.03.47.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.03.48.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 06.03.53.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.04.01.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.04.18.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.04.19.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.04.27.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.06.04.000000000','DD-MM-YYYY HH24.MI.SS.FF'),1 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.06.29.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.06.35.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.08.09.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.09.53.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
     select 10010124397,to_timestamp('01-05-2011 10.18.11.000000000','DD-MM-YYYY HH24.MI.SS.FF'),1 action_type from dual
    ),
    q1 as
    (select student_id,
            start_time,
            case when start_time - lag(start_time,1,start_time) over (partition by student_id order by start_time) > numtodsinterval(75,'minute')
                 then 1 
                 else action_type
            end action_type,
            row_number() over (partition by student_id order by start_time) rn,
            count(*) over (partition by student_id order by start_time
                           rows between unbounded preceding and unbounded following
                          ) rn_max
       from q
    ),
    q2 as
    (select student_id,
            start_time,
            rn,
            lead(rn,1,rn_max + 1) over (partition by student_id order by start_time) rn_next,
            rn_max,
            nullif(lead(student_id,1,1) over (partition by student_id order by start_time),student_id) status
       from q1
      where action_type = 1
    )
    select s.student_id,
           s.start_time,
           e.start_time end_time,
           e.start_time - s.start_time duration,
           nvl(s.rn_next,s.rn_max + 1) - s.rn - 1 heart_beat_cnt
      from q2 s,
           q1 e
     where e.student_id = s.student_id
       and e.rn = s.rn_next - 1
    Regards

    Etbin
  • 13. Re: SQl_CNT
    7688438 Newbie
    Currently Being Moderated
    Etbin,

    you are helping me alot.Thanks for your inputs.

    New requirement added:
    In some of my data i don't see action_type as '1' in my first records.if that is the case then treat or pretend action_type as 1.This case also should be added in the existing code.

    Sample data:


    Student_id start_time action_type
    10010124397     01-MAY-11 06.02.04.000000000 PM     2---SESSION LOGIN TIME(Treat or pretend this action_type as 1 )
    10010124397     01-MAY-11 06.02.15.000000000 PM     2
    10010124397     01-MAY-11 06.02.19.000000000 PM     2
    10010124397     01-MAY-11 06.02.25.000000000 PM     2
    10010124397     01-MAY-11 06.02.46.000000000 PM     2
    10010124397     01-MAY-11 06.03.31.000000000 PM     2
    10010124397     01-MAY-11 06.03.42.000000000 PM     2
    10010124397     01-MAY-11 06.03.47.000000000 PM     2
    10010124397     01-MAY-11 06.03.48.000000000 PM     1--Re login

    Thanks in advance

    k
  • 14. Re: SQl_CNT
    Etbin Guru
    Currently Being Moderated
    Maybe NOT TESTED!
    ...
    q1 as
    (select student_id,
            start_time,
            case when start_time - lag(start_time,1,start_time) over (partition by student_id order by start_time) > numtodsinterval(75,'minute')
    /* ===> */     or student_id != lag(student_id,1,0) over (partition by student_id order by start_time)   /* <=== this row added */
                 then 1 
                 else action_type
            end action_type,
            row_number() over (partition by student_id order by start_time) rn,
            count(*) over (partition by student_id order by start_time
                           rows between unbounded preceding and unbounded following
                          ) rn_max
       from q
    ),
    ...
    Regards

    Etbin
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points