9 Replies Latest reply: Feb 13, 2013 12:38 AM by 969918 RSS

    Data seperation Pls help

    969918
      hi all... i have a query...

      i have the following detail with me....

      Numbers Key abc column date
      12345 Y JVN 7/30/2010 20:45
      12345 N YVM 4/30/2011 1:45
      12354 Y JVN 6/18/2007 14:37
      12345 N YVM 5/13/2008 16:28

      i have N no of records like this. I need to write a query to find when the key underwent any change (from Y to N and from N to Y).

      This is for a single Numbers column(eg:12345). I have several numbers like this.

      Can someone help me with a querry for this???

      Thanks in advance

      Rajesh
        • 1. Re: Data seperation Pls help
          AlbertoFaenza
          Duplicate thread: {message:id=10840695}!!

          Don't repost the same question again and again.

          Regards.
          Al
          • 2. Re: Data seperation Pls help
            Etbin
            Maybe (boundary conditions not specified)
            select numbers,key,abc,column_date
              from (select numbers,key,abc,column_date,
                           case when key != lead(key,1) over (partition by numbers order by column_date)
                                  or key != lag(key,1) over (partition by numbers order by column_date)
                                then 'key change'
                           end key_change
                      from your_table
                   )
             where key_change is not null
            Regards

            Etbin

            Edited by: Etbin on 8.2.2013 11:09
            Oops! should look before posting. something I wasn't aware of

            Edited by: Etbin on 8.2.2013 11:12
            Sorry to be sorry ;) You were told to move here in Database General Forum
            • 3. Re: Data seperation Pls help
              Karthick_Arp
              Rajesh Chennai wrote:
              hi all... i have a query...

              i have the following detail with me....

              Numbers Key abc column date
              12345 Y JVN 7/30/2010 20:45
              12345 N YVM 4/30/2011 1:45
              12354 Y JVN 6/18/2007 14:37
              12345 N YVM 5/13/2008 16:28

              i have N no of records like this. I need to write a query to find when the key underwent any change (from Y to N and from N to Y).

              This is for a single Numbers column(eg:12345). I have several numbers like this.

              Can someone help me with a querry for this???

              Thanks in advance

              Rajesh
              You can use LAG analytic function and check if the key has changed. Something like this
              SQL> with t
                2  as
                3  (
                4  select 12345 no, 'Y' key, 'JVN' abc, to_date('7/30/2010 20:45', 'mm/dd/yyyy hh24:mi') column_date from dual union all
                5  select 12345, 'N', 'YVM', to_date('4/30/2011 1:45', 'mm/dd/yyyy hh24:mi') from dual union all
                6  select 12354, 'Y', 'JVN', to_date('6/18/2007 14:37', 'mm/dd/yyyy hh24:mi') from dual union all
                7  select 12345, 'N', 'YVM', to_date('5/13/2008 16:28', 'mm/dd/yyyy hh24:mi') from dual
                8  )
                9  select no, key, abc, column_date, decode(key, previous_key, ' ', 'CHANGED') change_status
               10    from (
               11            select t.*, nvl(lag(key) over(partition by no order by column_date), key) previous_key
               12              from t
               13         )
               14 /
               
                      NO K ABC COLUMN_DA CHANGE_
              ---------- - --- --------- -------
                   12345 N YVM 13-MAY-08
                   12345 Y JVN 30-JUL-10 CHANGED
                   12345 N YVM 30-APR-11 CHANGED
                   12354 Y JVN 18-JUN-07
               
              SQL> 
              • 4. Re: Data seperation Pls help
                969918
                but as we can find the first record is changed to N from Y... so there must be a "changed" indicator there also ryt?
                • 5. Re: Data seperation Pls help
                  969918
                  wel dats okay.... No aplogies pls...;)
                  • 6. Re: Data seperation Pls help
                    Karthick_Arp
                    Rajesh Chennai wrote:
                    but as we can find the first record is changed to N from Y... so there must be a "changed" indicator there also ryt?
                    with t
                    as
                    (
                    select 12345 no, 'Y' key, 'JVN' abc, to_date('7/30/2010 20:45', 'mm/dd/yyyy hh24:mi') column_date from dual union all
                    select 12345, 'N', 'YVM', to_date('4/30/2011 1:45', 'mm/dd/yyyy hh24:mi') from dual union all
                    select 12354, 'Y', 'JVN', to_date('6/18/2007 14:37', 'mm/dd/yyyy hh24:mi') from dual union all
                    select 12345, 'N', 'YVM', to_date('5/13/2008 16:28', 'mm/dd/yyyy hh24:mi') from dual
                    )
                    select no
                         , key
                         , abc
                         , column_date
                         , case when previous_key is null and key = 'N' then 'CHANGED'
                                when key != previous_key then 'CHANGED'
                                else '' end change_status
                      from (
                              select t.*, lag(key) over(partition by no order by column_date) previous_key
                                from t
                           )
                     
                    • 7. Re: Data seperation Pls help
                      EdStevens
                      Alberto Faenza wrote:
                      Duplicate thread: {message:id=10840695}!!

                      Don't repost the same question again and again.

                      Regards.
                      Al
                      Alberto,

                      Actually, that other thread was posted in the "General" forum, and the OP was directed to re-post in this forum, as it is more appropriate to his question. Yes, we get a lot of people spewing the same question all over the place, but in this case, the OP was doing as requested.
                      • 8. Re: Data seperation Pls help
                        969918
                        there are 100's of records like this, which makes it complex to include all of thm in a loop....
                        any other possibilities???
                        • 9. Re: Data seperation Pls help
                          jeneesh
                          WITH clause he has taken is for sample data..Just use your table..
                          select no
                               , key
                               , abc
                               , column_date
                               , case when previous_key is null and key = 'N' then 'CHANGED'
                                      when key != previous_key then 'CHANGED'
                                      else '' end change_status
                            from (
                                    select t.*, lag(key) over(partition by no order by column_date) previous_key
                                      from your_table t
                                 );