ORACLE确定两个日期之间的工作日数目

SQL> select ename,hiredate from emp;

ENAME      HIREDATE
---------- ---------------
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
JONES      02-APR-81
MARTIN     28-SEP-81
BLAKE      01-MAY-81
CLARK      09-JUN-81
SCOTT      19-APR-87
KING       17-NOV-81
TURNER     08-SEP-81
ADAMS      23-MAY-87

ENAME      HIREDATE
---------- ---------------
JAMES      03-DEC-81
FORD       03-DEC-81
MILLER     23-JAN-82

14 rows selected.

现在我们想知道WARD和JONES两名员工的HIREDATE之间相差多少个工作日。

SQL> select sum(case when to_char(jones_hd+t100.id-1,'DY') in ('Sat','Sun') then
 0 else 1 end) as days
  2  from
  3  (
  4  select
  5  max(case when ename='WARD' then hiredate end) as ward_hd,
  6  max(case when ename='JONES' then hiredate end) as jones_hd
  7  from emp
  8  where ename in('WARD','JONES')
  9  ) x,t100
 10  where t100.id<=jones_hd-ward_hd+1;

      DAYS
----------
        40

 

可能有人会想为什么这里要加个max()函数，其实目的就是为了去除NULL，下面做两个查询看看。

SQL> select
  2  (case when ename='WARD' then hiredate end) as ward_hd,
  3  (case when ename='JONES' then hiredate end) as jones_hd
  4  from emp
  5  where ename in('WARD','JONES');

WARD_HD         JONES_H