获取两个日期之间(假日除外)的工作日数的函数 [英] Function to get number of weekdays between two dates excluding holidays

问题描述

我有一个sql查询，它强制转换2个日期并检查它们是否相等.但是即使日期相等，我也没有得到结果.

I have an sql query that casts 2 dates and checks if they are equal. But even though the dates are equal, i dont get the result.

create or replace 
FUNCTION getWorkingDays(
  DATE_ONE DATE,
  DATE_TWO DATE)
RETURN NUMBER
IS
DAY_COUNT  NUMBER := 0;
START_DATE DATE;
END_DATE   DATE;
HOL_COUNT NUMBER := 0;
BEGIN        
IF(DATE_ONE                   IS NOT NULL AND DATE_TWO IS NOT NULL) THEN
  IF DATE_ONE                  < DATE_TWO THEN
    START_DATE                := DATE_ONE;
    END_DATE                  := DATE_TWO;
  ELSE
    START_DATE := DATE_TWO;
    END_DATE   := DATE_ONE;
  END IF;     
  WHILE START_DATE < END_DATE       
  LOOP      
    IF TO_CHAR(START_DATE,'DY') NOT IN ('SAT','SUN') THEN
      DAY_COUNT := DAY_COUNT         + 1;
    END IF;      
     SELECT count(*) INTO HOL_COUNT
FROM ATL_JOB_HOLIDAY jh
JOIN ATL_MASTER_JOB mj
ON MJ.MASTER_JOB_ID       = JH.MASTER_JOB_ID
WHERE TRUNC(HOLIDAY_DATE) = START_DATE;
     IF(HOL_COUNT >0) THEN
      DAY_COUNT := DAY_COUNT         + 1;
       Dbms_Output.put_line('Holiday333 :- IS A HOLIDAY');
       END IF; 
        START_DATE := START_DATE + 1;       
  END LOOP;
END IF;
RETURN DAY_COUNT;
EXCEPTION
WHEN OTHERS THEN
  RETURN NULL;
END getWorkingDays;'

推荐答案

我在您先前的问题中张贴了大部分内容.您无需遍历每一天并逐一检查.您应该能够计算工作日的天数，然后减去表中的不同天数:

I posted most of this in your earlier question. You do not need to loop through the days and check each one individually. You should be able to calculate the number of week days and then just subtract number of distinct days in your table:

CREATE FUNCTION getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE
) RETURN NUMBER
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
  p_holiday_days NUMBER;
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN NUll;
  END IF;

  p_start_date := TRUNC( LEAST( in_start_date, in_end_date ) );
  p_end_date   := TRUNC( GREATEST( in_start_date, in_end_date ) );

  -- 5/7 * ( Number of weekdays between monday of the week containing the start date
  --         and monday of the week containing the end date )
  -- + LEAST( day of week for end date, 5 )
  -- - LEAST( day of week for start date, 5 )
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ) + 1, 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ) + 1, 5 );

  SELECT COUNT( DISTINCT TRUNC( HOLIDAY_DATE ) )
  INTO   p_holiday_days
  FROM   ATL_JOB_HOLIDAY jh
  JOIN   ATL_MASTER_JOB mj
  ON     MJ.MASTER_JOB_ID    = JH.MASTER_JOB_ID
  WHERE  TRUNC(HOLIDAY_DATE) BETWEEN p_start_date AND p_end_date;

  RETURN p_working_days - p_holiday_days;
END;
/

(注意:在比较之前先对HOLIDAY_DATE，in_start_date和in_end_date进行TRUNC组合，以便有效地忽略所有时间分量.)

(Note: HOLIDAY_DATE, in_start_date and in_end_date are TRUNCated before comparing so that all the time components are effectively ignored.)

这篇关于获取两个日期之间(假日除外)的工作日数的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！