贴一个自认有点取巧的,关键是理解逻辑啊.

--add by xieh 2008-11-07

create or replace function FUNC_GET_ONLINETIME(
p_starttime in date, --开始时间,以半小是为步长
p_workno in number --工号
)
return number is
Result number; --半小时的在线时间(签入时间)
v_number1 number;--半小时内签出工作时间之和
v_number2 number;--半小时内未签出工作时间之和
begin
begin
select sum((endtime-begintime)*24*60*60) into v_number1 from(
select
case when t.actbegin<=p_starttime then p_starttime
else t.actbegin end as begintime, --实际开始时间
case when t.actend>=p_starttime+1/48 then p_starttime+1/48
else t.actend end as endtime --实际结束时间
from tagentoprinfo t
where t.agentid=p_workno
and t.operatetype=0
and t.actbegin < p_starttime+1/48--落在区间的记录
and t.actend >p_starttime--落在区间的记录
);
exception
when no_data_found then
v_number1 := 0;
end;

begin
select sum((endtime-begintime)*24*60*60) into v_number2 from
(
select
case when t1.actbegin<= p_starttime then p_starttime
else t1.actbegin end as begintime, --实际开始时间
p_starttime+1/48 as endtime --实际结束时间
from tagentoprinfo t1
where t1.agentid=p_workno
and t1.operatetype=1
and t1.actbegin < p_starttime+1/48 --落在区间的记录
and t1.actend > trunc(p_starttime,'dd')-2
and t1.actend < trunc(p_starttime,'dd')+2 --缩小查询范围,不会工作四天吧.
and rownum=1--应该只有一条才正常
);
exception
when no_data_found then
v_number2 := 0;
end;
if v_number1 is null then
v_number1 := 0;
end if;

if v_number2 is null then
v_number2 := 0;
end if;
Result := round((v_number1 + v_number2),0);
return(Result);

exception
when others then
Result := -1;
return(Result);
end FUNC_GET_ONLINETIME;