发现没有。到系统日期一个月的工作日/今天的日期是当月

Question

我使用以下查询来找出一个月中不包括周末的工作日数

select payroll_id as payrollId,
(select count(*) 
from ( select rownum rnum 
from all_objects 
where rownum <= to_date(to_char(last_day(to_date('01-'||'MAY'||'2017','DD-MM-YYYY')),'DD')||'MAY'||'2017','DD-MM-YYYY') - to_date('01-'||'MAY'||'2017','DD-MM-YYYY')+1 ) 
where to_char( to_date('01-'||'MAY'||'2017','DD-MM-YYYY')+rnum-1, 'DY' ) 
not in ( 'SAT', 'SUN' )) - (select count(*) from admin_holiday where to_char(holiday_date,'DD-MON-YYYY') like '%-MAY-2017%' and holiday_type_id=1) days
from employee where DEL_FLAG=1 order by payrollId

在这里，payrollId 是员工表中employee_id，admin_holiday是一个包含有关一个月中国定假日信息的表

我的要求是，如果月份是当月，那么工作日应该是今天的日期。例如，当前月份是MAY/2017，直到今天(即22/05/2017(，不包括周末的工作日为15(根据印度教日历，10/05/2017是国定假日。

如何获得所需的结果。

注意，此操作必须在单个选择语句中执行，并且不能执行其他 pl/sql 块等。

Answer 1

以下是我如何处理等效查询的方法。通过将日期范围计算放入小型交叉联接查询中，您可以在查询的其余部分轻松访问结果。

SELECT
     e.payroll_id AS payrollId
  ,  cj. working_days
FROM employee e
cross join (
        select
             GREATEST(NEXT_DAY(start_date, 'MON') - start_date - 2, 0) 
            + ((NEXT_DAY(end_date, 'MON') - NEXT_DAY(start_date, 'MON'))/7)*5
            - GREATEST(NEXT_DAY(end_date, 'MON') - end_date - 3, 0) 
            - (
                  SELECT COUNT(holiday) 
                  FROM admin_holiday
                  WHERE holiday_date BETWEEN start_date AND end_date
               ) 
              as working_days
        FROM (
              select
                 to_date('20170501','yyyymmdd')  as start_date
               , to_date('20170522','yyyymmdd')  as end_date
              from dual
            )
        ) cj
WHERE e.DEL_FLAG = 1
ORDER BY e.payrollId

工作日的逻辑是：

计算第一周的前导天数 +(计算周一/周五期间的数量(* 5 - 最后一周的过去天数 - 表中的额外假期：

select
      GREATEST(NEXT_DAY(start_date, 'MON') - start_date - 2, 0) 
    + ((NEXT_DAY(end_date, 'MON') - NEXT_DAY(start_date, 'MON'))/7)*5
    - GREATEST(NEXT_DAY(end_date, 'MON') - end_date - 3, 0) 
    - (
          SELECT COUNT(holiday) 
          FROM admin_holiday
          WHERE holiday_date BETWEEN start_date AND end_date
       ) 
      as working_days
    , start_date
    , end_date
    , holidays
    , end_date - start_date
FROM (
      select
         trunc(sysdate,'MM')  as start_date
       , trunc(sysdate)       as end_date
      from dual
    )

示例(这里没有零假期(：

+--------------+--------------+-------------+------------+-------------------------+
| WORKING_DAYS | START_DATE   | END_DATE    | HOLIDAYS   |   END_DATE-START_DATE   |
+--------------+--------------+-------------+------------+-------------------------+
|           16 |  01.05.2017  | 22.05.2017  |          0 |                      21 |
+--------------+--------------+-------------+------------+-------------------------+

要手动设置日期，您可以执行以下操作：

select
      GREATEST(NEXT_DAY(start_date, 'MON') - start_date - 2, 0) 
    + ((NEXT_DAY(end_date, 'MON') - NEXT_DAY(start_date, 'MON'))/7)*5
    - GREATEST(NEXT_DAY(end_date, 'MON') - end_date - 3, 0) 
    - (
          SELECT COUNT(holiday) 
          FROM admin_holiday
          WHERE holiday_date BETWEEN start_date AND end_date
       ) 
      as working_days
FROM (
      select
         to_date('20170501','yyyymmdd')  as start_date
       , to_date('20170522','yyyymmdd')  as end_date
      from dual
    )