1 2 Previous Next 18 Replies Latest reply: Apr 29, 2013 3:29 AM by Etbin RSS

    SQl

    7688438
      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
          >
          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
            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
              €$ħ₪
              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
                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
                  >
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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