2 Replies Latest reply on Jun 4, 2013 1:55 PM by Wael Abu Rezeq

    rownum returning value

    Wael Abu Rezeq
      Dear All,

      I have a table containing some records (Customer, DataOfPurchasments) where a customer might be purchasing in many different days. If I am willing to measure the inactivity period for this customer, what I have to do?

      I was thinking to sort the customers ASC into a new table as to sort the rownum for each, the query is as below:

      create table t as (Select customer,dataofPurch From sales) order by 1,2
      then what I get is a sorted data regarding rownum as below:

      Rownum, Customer, Dt
      1, 1234, 01-Feb-2013
      2, 1234, 05-Feb-2013
      3, 1234, 06-feb-2013
      4, 5678, 06-Feb-2013
      5, 5678, 08-Feb-2013

      The question, what I have to do if I want the output to be as below:

      Customer, Date1,Date2,Inactivity
      1234, 01-Feb-2013,05-Feb-2013,4
      1234, 05-Feb-2013,06-Feb-2013,1
      5678, 06-Feb-2013,08-Feb-2013,2

      I was thinking in a query such as

      Select Customer,Dt Date 1,X.Dt Date2 ,Dt-X.Dt
      From t, (Select customer, Dt From T where rownum <rownum+1)X
      where t.Customer = X.customer
      group by Customer,Dt Date 1,X.Dt Date2 ,Dt-X.Dt

      but that wasn't helpful. So, How can I solve my problem?

      Regards;
        • 1. Re: rownum returning value
          MLBrown
          You would probably have better luck asking this type of question in the SQL/PLSQL forum: {forum:id=75}, but based on what I saw you just need to use Analytics - LEAD() in particular and you don't even need to create another table. LEAD() will allow you to look at the next row of data and use it in your query. Here is a query that you could use but you would need to modify it slightly:
          with test_data 
            as (select 1234 customer, to_date('02012013','MMDDYYYY') order_date from dual union all
                select 1234 customer, to_date('02052013','MMDDYYYY') order_date from dual union all
                select 1234 customer, to_date('02062013','MMDDYYYY') order_date from dual union all
                select 5678 customer, to_date('02062013','MMDDYYYY') order_date from dual union all
                select 5678 customer, to_date('02082013','MMDDYYYY') order_date from dual)
           select customer, 
                  order_date, 
                  lead(order_date, 1, null) OVER (PARTITION BY customer ORDER BY order_date NULLS LAST) next_order_date,
                  lead(order_date, 1, null) OVER (PARTITION BY customer ORDER BY order_date NULLS LAST) - order_date num_days   
             from test_data  
            order by customer, order_date;
          Your query would probably look something like:
          select customer, 
                 dataofPurch, 
                 lead(dataofPurch, 1, null) OVER (PARTITION BY customer ORDER BY dataofPurch NULLS LAST) next_dataofPurch,
                 lead(dataofPurch, 1, null) OVER (PARTITION BY customer ORDER BY dataofPurch NULLS LAST) - dataofPurch num_days   
            from sales 
           order by customer, dataofPurch;
          Hope this helps, but if not post the question in the SQL/PLSQL forum.
          • 2. Re: rownum returning value
            Wael Abu Rezeq
            you are right and this was amazing.. But I was wondering if we can use the rownum concept as to avoid built in functions