oracle-计算工作日

数据库模拟表如下

operate_id	operate_type	operate_date	process_sn
1	GD	2013-09-15 17:18:37	10001
2	JD	2013-09-13 17:18:42	10001
3	GD	2013-09-19 17:18:42	10002
4	JD	2013-09-16 17:18:42	10002

 

查询出表中超时处理的单号数（即GD时间-JD时间>2个工作日的process_sn数)

ORACLE中时间段的工作日计算方法如下

select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date('2013-09-12','yyyy-MM-dd') - to_date('2013-09-01','yyyy-MM-dd')+1 )
where to_char( to_date('2013-09-01','yyyy-MM-dd')+rnum-1, 'D' ) 
not in ( '1', '7' )

直接运用在查询条件中报ORA-01841错误如下（oracle optimizer--查询转换）

select count(a.process_sn)
from tbl_operate_process a,tbl_operate_process b where a.process_sn=b.process_sn and a.operate_type='GD' and b.operate_type='JD'
and (select count(*) 
from ( select rownum rnum
from all_objects
where rownum <= to_date(substr(a.operate_date,0,10),'yyyy-MM-dd') - to_date(substr(b.operate_date,0,10),'yyyy-MM-dd')+1 )
where to_char( to_date(substr(b.operate_date,0,10),'yyyy-MM-dd')+rnum-1, 'D' )
not in ( '1', '7' ))>2

 经过测试先建立function，再在查询条件中用function作为条件即可

create or replace function fun_workday(startdate in varchar2,enddate in varchar2)return number
IS
TOTALVALUE NUMBER;
begin
select count(*) into TOTALVALUE
from ( select rownum rnum
from all_objects
where rownum <= to_date(enddate,'yyyy-MM-dd') - to_date(startdate,'yyyy-MM-dd')+1 )
where to_char( to_date(startdate,'yyyy-MM-dd')+rnum-1, 'D' )
not in ( '1', '7' );
return TOTALVALUE;
end;

select count(a.process_sn) 
from tbl_operate_process a,tbl_operate_process b 
where a.process_sn=b.process_sn and a.operate_type='GD' and b.operate_type='JD' and 
(select fun_workday(substr(b.operate_date,0,10),substr(a.operate_date,0,10)) from dual)>2