Re: tricky SQL query (currently handled through PL/SQL)

One final update based on using the "partition by" clause of the analytic functions.

SELECT mn MY_NAME, mc MY_CODE, t_1, t_2
  FROM (

        SELECT mn, mc,
               decode(b, mc, sysdate+NULL, md) T_1,
               decode(a, mc, lead(md) over (
                             partition by mn order by md), md) T_2
          FROM (
                SELECT my_name MN, my_code MC, my_date MD,
                       nvl(lag(my_code) over (
                           partition by my_name order by my_date), -1) B,
                       nvl(lead(my_code) over (
                           partition by my_name order by my_date), -1) A
                  FROM my_table_v1
               )
         WHERE b != mc
            OR a != mc
       )

• jetlag --