All Forums Analytics
18 Sep 2014
can we have Rank function using rows between 1 preceding 1 preceding

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 

dnoeth 4628 posts Joined 11/04
19 Sep 2014

You can utilize the RESET WHEN option.

   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



19 Sep 2014

Thanks a lot Dieter .

09 Jun 2015

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_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 

You must sign in to leave a comment.