## 3 replies

Last post Jun 04, 2013 09:47 AM by Wael Abu Rezeq

Member

6 Points

43 Posts

### Data sequentially operation - Rownum

Jun 03, 2013 10:34 AM|Wael Abu Rezeq|LINK

<div class="jive-message-body">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;</div>

Contributor

3252 Points

1240 Posts

### Re: Data sequentially operation - Rownum

What database are you using

and does it have Analytic Functions so that you can PARTITION and ORDER customers creating a WINDOW of the customer so you can do inactivity calculations for that customer.  (Oracle has many Analytic Functions)

Still a bit unclear on the end game.

For a given customer are you looking for the greatest period of inactivity? Or some other dimension, like rank ordering periods of inactivity.

Member

6 Points

43 Posts

### Re: Data sequentially operation - Rownum

Jun 04, 2013 09:24 AM|Wael Abu Rezeq|LINK

1)I am using Oracle

2) I don't think that we need to partition, we have just done the oreder by to sort customer with thier dates.

3) currently, I am looking to get the output as I wrote (Table 2) before then many functions can be applied..

Br,

Member

6 Points

43 Posts

### Re: Data sequentially operation - Rownum

Jun 04, 2013 09:47 AM|Wael Abu Rezeq|LINK

The below did solve my problem, many thanks for your kind co-operation

lead ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )