请问ORACLE大神，下面的情况SQL语句怎么写

我用的ORACLE数据库，如图

这个是一张表里面的数据，我怎么判断两个送审之间并且在组长审批通过之后有退回，只要有退回次数就加1，如果有多个退回次数也只加1，而且可能有很多个送审，注意是送审之间并且在组长审批通过之后，如果是送审之间组长审批没通过，就不算。组长审批之前的也不算，必须是组长审批通过之后的退回才算。。。请问ORACLE大神们，这样的SQL语句该怎么写啊。。谢谢了~~~
我这个是通过时间排序后的结果集,我这个是用在存储过程里面的，也可以分成很多条SQL语句，要怎么做啊~~

if exists(select * from sys.tables where NAME='tmp_tbl')
begin
 drop table tmp_tbl;
end
create table tmp_tbl(
 ACTIVITY_NAME_ varchar(40),
 TRANSITION_ varchar(40),
 ACTIVITY_TIME_ DATETIME
);

insert into tmp_tbl values('录入','送审','2000-01-02');
insert into tmp_tbl values('组长审批','审批通过','2000-01-03');
insert into tmp_tbl values('主管审批','审批通过','2000-01-04');
insert into tmp_tbl values('录入','送审','2000-01-05');
insert into tmp_tbl values('组长审批','退回','2000-01-06');
insert into tmp_tbl values('录入','送审','2000-01-07');
insert into tmp_tbl values('组长审批','审批通过','2000-01-08');
insert into tmp_tbl values('主管审批','退回','2000-01-09');
insert into tmp_tbl values('组长审批','审批通过','2000-01-10');
insert into tmp_tbl values('主管审批','审批通过','2000-01-11');

--排序
select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl
--划分送审间隔
select number,isnull((select min(number) from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tbl1 where ACTIVITY_NAME_='录入' and TRANSITION_='送审' and tmp_tbl1.number>tmp_tblss.number),(select count(*) from tmp_tbl)+1) 'number1'
from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblss
where ACTIVITY_NAME_='录入' and TRANSITION_='送审'
--查询组长审批通过数据
select tmp_tblzz.number,tmp_tblss.number,tmp_tblss.number1
from (select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblzz
 left join(select number,isnull((select min(number) from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tbl1 where ACTIVITY_NAME_='录入' and TRANSITION_='送审' and tmp_tbl1.number>tmp_tblss.number),(select count(*) from tmp_tbl)+1) 'number1'
  from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblss
  where ACTIVITY_NAME_='录入' and TRANSITION_='送审')tmp_tblss on tmp_tblzz.number between tmp_tblss.number and tmp_tblss.number1
where ACTIVITY_NAME_='组长审批' and TRANSITION_='审批通过'
--查询退回数据
select *
from (select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblth
 left join (select number,isnull((select min(number) from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tbl1 where ACTIVITY_NAME_='录入' and TRANSITION_='送审' and tmp_tbl1.number>tmp_tblss.number),(select count(*) from tmp_tbl)+1) 'number1'
   from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblss
   where ACTIVITY_NAME_='录入' and TRANSITION_='送审'
  ) tmp_tblss on tmp_tblth.number between tmp_tblss.number and tmp_tblss.number1
 left join (select tmp_tblzz.number,tmp_tblss.number 'number2',tmp_tblss.number1
   from (select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblzz
    left join(select number,isnull((select min(number) from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tbl1 where ACTIVITY_NAME_='录入' and TRANSITION_='送审' and tmp_tbl1.number>tmp_tblss.number),(select count(*) from tmp_tbl)+1) 'number1'
     from(select row_number() over(order by ACTIVITY_TIME_) 'number',* from tmp_tbl)tmp_tblss
     where ACTIVITY_NAME_='录入' and TRANSITION_='送审')tmp_tblss on tmp_tblzz.number between tmp_tblss.number and tmp_tblss.number1
   where ACTIVITY_NAME_='组长审批' and TRANSITION_='审批通过'
  ) tmp_tblzz on tmp_tblss.number=tmp_tblzz.number2 and tmp_tblss.number1=tmp_tblzz.number1 and tmp_tblth.number>tmp_tblzz.number
where TRANSITION_='退回'

我写的是SQLSERVER语法