1 2 Previous Next 18 Replies Latest reply: Apr 29, 2013 3:29 AM by Etbin Go to original post RSS
      • 15. Re: SQl_CNT
        7688438
        Etbin,

        Thanks for your input.my requirement for full load is satisfied.But for the incremental load i need to get the last session for each student into a look up table to get the complete session information for incremental loads(so that i can union session_lkp table and tracking table in the incremental load)


        For ex:

        Category 1:(Here last record action_type is 2)

        Student_id start_time action_type
        10010124397     01-MAY-11 06.02.04.000000000 PM     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     1----(this is last session)
        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



        Category 2:(Here last record action_type is 1)

        Student_id start_time action_type
        10010124398     01-MAY-11 06.02.04.000000000 PM     1
        10010124398     01-MAY-11 06.02.15.000000000 PM     2
        10010124398     01-MAY-11 06.02.19.000000000 PM     2
        10010124398     01-MAY-11 06.02.25.000000000 PM     2
        10010124398     01-MAY-11 06.02.46.000000000 PM     1
        10010124398     01-MAY-11 06.03.31.000000000 PM     2
        10010124398     01-MAY-11 06.03.42.000000000 PM     2
        10010124398     01-MAY-11 06.03.47.000000000 PM     2
        -----------------------------------------------------------------------------------------------------------------
        10010124398     01-MAY-11 06.03.48.000000000 PM     1..(this is last session)

        now i need to insert the last session from this table into a new look up table called Session_lkp with the same structure as the above table:

        Out put in Session_lkp should be:
        /* below data is from category 1*/
        10010124397     01-MAY-11 06.02.46.000000000 PM     1
        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
        /* below data is from category 2 */
        10010124398     01-MAY-11 06.03.48.000000000 PM     1


        Thanks in advance

        K

        Edited by: 7688438 on Apr 28, 2013 6:02 PM

        Edited by: 7688438 on Apr 28, 2013 6:07 PM

        Edited by: 7688438 on Apr 28, 2013 6:08 PM
        • 16. Re: SQl_CNT
          sb92075
          how many more years before you can write your own SQL?


          Handle:     7688438
          Status Level:     Newbie
          Registered:     Jul 9, 2009
          Total Posts:     125
          Total Questions:     75 (48 unresolved)

          I extend to you my condolences since you rarely get your questions answered.
          • 17. Re: SQl_CNT
            7688438
            I tried it. But I was not able to get it.i registered in 2009. But not purely working on oracle SQL.

            Thanks for ur help experts

            K
            • 18. Re: SQl_CNT
              Etbin
              Never worked as much on an answered question ;)
              with
              q as 
              (select 10010124396 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 10010124396,to_timestamp('01-05-2011 06.02.15.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
               select 10010124396,to_timestamp('01-05-2011 06.02.19.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
               select 10010124396,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 10010124398,to_timestamp('01-05-2011 10.06.04.000000000','DD-MM-YYYY HH24.MI.SS.FF'),1 action_type from dual union all
               select 10010124398,to_timestamp('01-05-2011 10.06.29.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
               select 10010124398,to_timestamp('01-05-2011 10.06.35.000000000','DD-MM-YYYY HH24.MI.SS.FF'),1 action_type from dual union all
               select 10010124398,to_timestamp('01-05-2011 10.08.09.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
               select 10010124398,to_timestamp('01-05-2011 10.09.53.000000000','DD-MM-YYYY HH24.MI.SS.FF'),2 action_type from dual union all
               select 10010124399,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')
                             or student_id != lag(student_id,1,0) over (partition by student_id
                                                                            order by start_time
                                                                       )
                           then 1 
                           else action_type
                      end action_type
                 from q
              ),
              q2 as
              (select student_id,
                      start_time,
                      action_type,
                      row_number() over (order by start_time) - row_number() over (partition by student_id,action_type
                                                                                       order by start_time
                                                                                  ) login_group
                 from q1
              ),
              q3 as
              (select student_id,
                      start_time,
                      row_number() over (partition by student_id
                                             order by start_time desc
                                        ) last_login
                 from q2
                where action_type = 1
              )
              select q.student_id,
                     q.start_time,
                     q.action_type
                from q,
                     q3
               where q.student_id = q3.student_id
                 and q.start_time >= q3.start_time
                 and q3.last_login = 1
              Regards

              Etbin
              1 2 Previous Next