can we have Rank function using rows between 1 preceding 1 preceding

Analytics

can we have Rank function using rows between 1 preceding 1 preceding

Hi,

Userid , date ,channeltype and want to create seq (rank)

A      1/jan/2014   email    1

A      2/JAN/2014    chat      2

A      3/jan/2014      email   3 

A       10/jan/2014     email   1 -- since the previous record date and current record date difference is > 72 hr  

Basically want to rank the data  group on userid order by date . If previous record date and next record date depending on channle if channel is chat only 48 hr diff is then rank it 2 else restart the rank as 1 again . If its email then previous record and current record date difference is 72  hr then rank it incremental else restart the counter .

ie (3/jan - 2/jan) <= 2 days rank is increment to 3 else for 10/jan -3 jan  <> 3 days ie rank start with 1 .How restate the rank for partiton on userid  but depending date difference 

3 REPLIES
Senior Apprentice

Re: can we have Rank function using rows between 1 preceding 1 preceding

You can utilize the RESET WHEN option.

Untested:

select
...,
row_number()
over (partition by userid
order by date
reset when date - case when channel type = 'email' then 3 else 2 end
> min(date)
over (partition by userid
order by date)
from tab

Re: can we have Rank function using rows between 1 preceding 1 preceding

Thanks a lot Dieter .

Re: can we have Rank function using rows between 1 preceding 1 preceding

Hi ,

Sorry for posting new  question in old thread coz i was not able to create a new thread for posting. 

Question for Transposing the data from Columns to Row and long with that keep its parent child relationship  with ids eg

Emp_Name Manger_Name  Director_name

'aa'              'bb'                      'cc' 

'xx'              'yy'                     'zz'

Now the new table would be 

Emp_information

Emp_id  Emp_name  reporting_person_id  level 

1            'zz'                 Null                         1

2            'cc'                  null                         1

3            'yy'                   1                            2

4            'bb'                    2                           2

5            'aa'                    4                           3

6            'xx'                    3                           3 

Many thanks in advance