存储过程中的统计问题，在线等！

[碧水幽幽泉]

想在存储过程中再加入两个参数对下列的存储过程的结果集进行统计:
统计开始时间:stat_starttime date 统计结束时间:stat_endtime date
即止显示的t_overtime和t_leave中的starttime、endtime在stat_starttime～stat_endtime 内的信息

附存储过程:
create or replace procedure p_cok_stat_query_byEmpNo(
/**
* PURPOSE : 考勤统计查询,此过程由Java控制事务。
* REVISION>
Version Date Author Description
-------- -------- ---------- -------------------
V1.0D10 20091217 hbq 1. 创建存储过程
*/
str_empno in t_programinfo.programno%type, --项目编号
cur_result out sys_refcursor --结果集
)
is
begin

open cur_result for

select * from (
with tmp_employee as (
select empno, empname
from t_employeeinfo
where empno = str_empno
)
select empno, empname,over_startime,over_endtime,leave_startime, leave_endtime,
nvl(overtimenum, 0), nvl(leavetimenum, 0),
nvl(sum(overtimenum - leavetimenum) over(partition by empno), 0) remaintime
from (
select e.empno, e.empname,
a.starttime over_startime, a.endtime over_endtime,
'0' leave_startime,
'0' leave_endtime,
a.overtimenum,
0 leavetimenum,
1 over_or_leave
from tmp_employee e, t_overtime a
where e.empno = a.empno(+)
union all
select e.empno, e.empname,
'0' over_startime, '0' over_endtime,
a.starttime leave_starttime, a.endtime leave_endtime,
0 overtimenum,
a.leavetimenum, 2 over_or_leave
from tmp_employee e, t_leave a
where e.empno = a.empno(+)
)
);

end;
/

[碧水幽幽泉]

另加3表建表语句:
create table t_leave
(
leaveid varchar2(20) not null,
empno varchar2(20) not null,
starttime varchar2(30) not null,
endtime varchar2(30) not null,
leavereason varchar2(400) not null,
applytime varchar2(30) not null,
status number(4) not null,
leavetimenum number(4),
nextapprovedutyid number(2) not null
)
/
create table t_overtime
(
overtimeid varchar2(20) not null,
empno varchar2(20) not null,
starttime varchar2(30) not null,
endtime varchar2(30) not null,
overtimenum number(4),
overtimereason varchar2(400) not null,
status number(4) not null,
nextapprovedutyid number(2) not null,
applytime varchar2(30) not null,
programno varchar2(20) not null
)
/
create table t_employeeinfo
(
empno varchar2(20) not null,
empname varchar2(20),
empsex varchar2(3),
deptno varchar2(20),
positionno number,
vacancy number,
phonenumber varchar2(20),
worktime varchar2(20),
email varchar2(40),
remark varchar2(400),
extension varchar2(20),
address varchar2(400)
)
/

[super_sealOne]

没看懂要统计什么东西

[crazylaa]

没看懂。。。楼主还是搞些数据，以及想要什么效果来吧。。。

[crazylaa]

这个函数 f_cokstat_equal_time(a.starttime, a.endtime)
和这个函数 f_cokstat_record_one(i_cout_st,
i_cout_et,
to_date(substr(t.starttime, 1, 10), 'yyyy-mm-dd'),
to_date(substr(t.endtime, 1, 10), 'yyyy-mm-dd')
)
的返回值是啥类型啊？
如果是整型，按照
sum(decode(over_or_leave, 1, overtime_total, 2, -overtime_total, 0)) over(partition by empno)
一样，命名一样的别名，直接sum(A-B)就好了啊。

[碧水幽幽泉]

帮忙分析下sql语句：
注意注释：--要求此句能计算：A-B
select empno, empname, over_startime,over_endtime,leave_startime, leave_endtime,
nvl(overtimenum, 0), nvl(leavetimenum, 0),
sum(decode(over_or_leave, 1, overtime_total, 2, -overtime_total, 0)) over(partition by empno) --要求此句能计算：A-B -- sum(overtime_total)over(partition by empno) - sum(leavetime_total)over(partition by empno)
remaintime
from (
select e.empno, e.empname,
a.starttime over_startime, a.endtime over_endtime,
nvl(a.overtimenum, 0) overtimenum,
'0' leave_startime, '0' leave_endtime,
0 leavetimenum,
f_cokstat_equal_time(a.starttime, a.endtime) overtime_total, --A 1 over_or_leave
from t_employeeinfo e, t_overtime a
where e.empno = a.empno(+)

union all

select e.empno, e.empname,
'0' over_startime, '0' over_endtime,
0 overtimenum,
t.starttime leave_starttime, t.endtime leave_endtime,
nvl(t.leavetimenum,0) leavetimenum,
f_cokstat_record_one(i_cout_st,
i_cout_et,
to_date(substr(t.starttime, 1, 10), 'yyyy-mm-dd'),
to_date(substr(t.endtime, 1, 10), 'yyyy-mm-dd')
) leavetime_total, --B 2 over_or_leave
from t_employeeinfo e, t_leave t
where trunc(to_date(t.starttime,'yyyy-mm-dd hh24')) <= to_date(cok_cout_et,'yyyy-mm-dd')


and trunc(to_date(t.endtime,'yyyy-mm-dd hh24')) >= to_date(cok_cout_st,'yyyy-mm-dd')
and e.empno = t.empno(+)
);