oracle-计算工作日

时间:2024-04-30 17:38:02

数据库模拟表如下

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 ( '', '' )

直接运用在查询条件中报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 ( '', '' ))>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 ( '', '' );
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