복붙노트

[SQL] 오라클 SQL에서 계산 일 (아무 기능이나 절차)

SQL

오라클 SQL에서 계산 일 (아무 기능이나 절차)

나는 선택 오라클에서 두 날짜 사이 일 계산하기 위해 노력하고 있습니다. 내 계산이 대부분의 결과는 (I Excel에서 NETWORKDAYS과 비교) 주어진 날짜를 정정 줄 때 나는 지점에 도착하지만, 경우에 따라서는 -2 일 2 일간에 따라 다릅니다 - 나는 이유를 모르겠어 ...

여기 내 코드는 :

SELECT
((to_char(CompleteDate,'J') - to_char(InstallDate,'J'))+1) - (((to_char(CompleteDate,'WW')+ (52 * ((to_char(CompleteDate,'YYYY') - to_char(InstallDate,'YYYY'))))) - to_char(InstallDate,'WW'))*2) as BusinessDays
FROM TABLE

감사!

해결법

  1. ==============================

    1.마지막으로이 솔루션 :

    마지막으로이 솔루션 :

    SELECT OrderNumber, InstallDate, CompleteDate,
  (TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
  ((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
  (CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
  (CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;

    모든 답변에 감사드립니다!

  2. ==============================

    2.나는 모든 다른 위에서 논의 된 접근 방법 고려했다 우리에게 두 날짜 사이의 연도의 각 달에 작업 일 수를 제공하는 간단한 쿼리를 내놓았다 :

    나는 모든 다른 위에서 논의 된 접근 방법 고려했다 우리에게 두 날짜 사이의 연도의 각 달에 작업 일 수를 제공하는 간단한 쿼리를 내놓았다 :

    함께 test_data AS   (      시작일 AS SELECT TO_DATE ('01 잰 14 ')             TO_DATE 종료일 AS ('31 -dec-14 ')        이중 FROM   ), all_dates AS (     SELECT td.start_date, td.end_date, td.start_date + LEVEL 1 WEEK_DAY로서       test_data TD FROM    CONNECT BY LEVEL td.start_date의 + 1 <= td.end_date) SELECT TO_CHAR (WEEK_DAY, 'MON'), COUNT (*)   all_dates FROM  WHERE TO_CHAR (WEEK_DAY, '마구', 'nls_date_language = 미국인') NOT IN ( '태양', '앉아')  GROUP BY TO_CHAR (WEEK_DAY, 'MON');

    필요에 따라 쿼리를 수정 해 주시기 바랍니다.

  3. ==============================

    3.이 시도:

    이 시도:

    with holidays as 
(
select d from (
select minDate + level -1 d
 from (select min(submitDate) minDate, max (completeDate) maxDate
 from t)
 connect by level <= maxDate - mindate + 1) 
 where to_char(d, 'dy', 'nls_date_language=AMERICAN') not in ('sun' , 'sat')
)
select t.OrderNo, t.submitDate, t.completeDate, count(*) businessDays
from t join holidays h on h.d between t.submitDate and t.completeDate
group by t.OrderNo, t.submitDate, t.completeDate
order by orderno

    여기 sqlfiddle 데모입니다

  4. ==============================

    4.그 표시된 최종 솔루션은 항상 정확하지 않습니다를 참조하십시오. InstallDate는 매월 1 일이며, 가정 (토요일 떨어지는 경우) 및 CompleteDate이 달 16 일입니다 (만약 일요일 폭포)

    그 표시된 최종 솔루션은 항상 정확하지 않습니다를 참조하십시오. InstallDate는 매월 1 일이며, 가정 (토요일 떨어지는 경우) 및 CompleteDate이 달 16 일입니다 (만약 일요일 폭포)

    이 경우, 실제 영업일이 10 만 표시된 쿼리 결과는 12 그래서 같은 대답을 줄 것이다, 우리는 내가 사용한도의 경우이 유형을 치료해야

    (CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END

    그것을 처리하기 위해 라인.

    SELECT OrderNumber, InstallDate, CompleteDate,
(TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 - 
((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END)as BusinessDays
FROM Orders
ORDER BY OrderNumber;
  5. ==============================

    5.나는 더 읽기와 버스의 반환 수에 내 예를 바꿨다. 일 사이. 당신이 'J'- 줄리안 포맷을해야하는 이유를 알고 싶습니다. 걸리는 모든 시작 / 설치하고 끝 / 완료 날짜입니다. 이 작업을 사용하여 두 날짜 사이의 일의 정확한 수를 얻을 것이다. ... 당신과 나의 날짜를 바꾸기 필요한 경우 NLS를 추가 :

    나는 더 읽기와 버스의 반환 수에 내 예를 바꿨다. 일 사이. 당신이 'J'- 줄리안 포맷을해야하는 이유를 알고 싶습니다. 걸리는 모든 시작 / 설치하고 끝 / 완료 날짜입니다. 이 작업을 사용하여 두 날짜 사이의 일의 정확한 수를 얻을 것이다. ... 당신과 나의 날짜를 바꾸기 필요한 경우 NLS를 추가 :

     SELECT Count(*) BusDaysBtwn
  FROM
  (
  SELECT TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1 InstallDate  -- MON or any other day 
       , TO_DATE('2013-02-25', 'YYYY-MM-DD') CompleteDate           -- MON or any other day
       , TO_CHAR(TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1, 'DY') InstallDay   -- day of week
    FROM dual 
  CONNECT BY LEVEL <= (TO_DATE('2013-02-25', 'YYYY-MM-DD') - TO_DATE('2013-02-18', 'YYYY-MM-DD')) -- end_date - start_date 
   )
   WHERE InstallDay NOT IN ('SAT', 'SUN')
  /

  SQL> 5
  6. ==============================

    6.허용되는 솔루션은 아주 가까이 있지만 어떤 경우에는 잘못된 것 (예를 들어, 2015년 2월 1일 2015년 5월 31일을 통해 2-28 / 2015 또는 2015년 5월 1일를 통해). 여기에 정제 된 버전입니다 ...

    허용되는 솔루션은 아주 가까이 있지만 어떤 경우에는 잘못된 것 (예를 들어, 2015년 2월 1일 2015년 5월 31일을 통해 2-28 / 2015 또는 2015년 5월 1일를 통해). 여기에 정제 된 버전입니다 ...

      end_date-begin_date+1 /* total days */
  - TRUNC(2*(end_date-begin_date+1)/7) /* weekend days in whole weeks */
  - (CASE
      WHEN TO_CHAR(begin_date,'D') = 1 AND REMAINDER(end_date-begin_date+1,7) > 0 THEN 1
      WHEN TO_CHAR(begin_date,'D') = 8 - REMAINDER(end_date-begin_date+1,7) THEN 1
      WHEN TO_CHAR(begin_date,'D') > 8 - REMAINDER(end_date-begin_date+1,7) THEN 2
      ELSE 0
    END) /* weekend days in partial week */
  AS business_days

    7 (전체 주)의 배수를 처리하는 부분이 좋다. 주 부분 부를 고려할 때, 다음과 같은 행렬에 따른 오프셋 요일 및 부분적인 부분 일 수 모두에 의존 ...

       654321
1N 111111
2M 100000
3T 210000
4W 221000
5R 222100
6F 222210
7S 222221
  7. ==============================

    7.다만 일요일 및 당신이 사용할 수있는 토요일을 제거하려면

    다만 일요일 및 당신이 사용할 수있는 토요일을 제거하려면

    SELECT Base_DateDiff
     - (floor((Base_DateDiff + 0 + Start_WeekDay) / 7))
     - (floor((Base_DateDiff + 1 + Start_WeekDay) / 7))
FROM   (SELECT 1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW') Start_WeekDay
             , CompleteDate - InstallDate + 1 Base_DateDiff
        FROM TABLE) a

    Base_DateDiff는 두 날짜 사이의 일 수를 계산 (층 ((Base_DateDiff + 0 + START_WEEKDAY) / 7)) 일 수를 카운트 (층 ((Base_DateDiff + 1 + START_WEEKDAY) / 7)) 토 개수를 카운트

    1 + TRUNC (InstallDate) - TRUNC (InstallDate는 'IW') 일요일 7 월요일 1을 얻을

  8. ==============================

    8.이 쿼리는 주어진 날짜에서 역 N 일 이동하는 데 사용할 수 있습니다 (영업일 만)

    이 쿼리는 주어진 날짜에서 역 N 일 이동하는 데 사용할 수 있습니다 (영업일 만)

    예를 들어, 2017년 5월 17일에서 뒤로 십오일 이동 :

    select date_point, closest_saturday - (15 - offset + floor((15 - offset) / 6) * 2) from(
   select date_point,
          closest_saturday,
          (case
             when weekday_num > 1 then
              weekday_num - 2
             else
              0
           end) offset
    from (
           select  to_date('2017-05-17', 'yyyy-mm-dd') date_point,
                   to_date('2017-05-17', 'yyyy-mm-dd') - to_char(to_date('2017-05-17', 'yyyy-mm-dd'), 'D') closest_saturday,
                   to_char(to_date('2017-05-17', 'yyyy-mm-dd'), 'D') weekday_num
           from dual
          ))

    일부 간단한 설명 : 우리는 주어진 날짜에서 역 N 일 이동한다고 가정 -보다 작거나 주어진 날짜에 동일 가장 가까운 토요일을 찾습니다. - (- 오프셋 N)이 일에 가장 가까운 토요일에서 다시 병실로 이동합니다. 가장 가까운 토요일과 지정된 일자 (지정된 일자 제외) 사이의 영업일 수는 오프셋.

    * (영업일 만) 토요일에서 M 일 돌아가려면이 공식 DateOfMonthOfTheSaturday를 사용 - [M + 층 (M / 6) * 2]

  9. ==============================

    9.여기 빠르고 유연 함수이다. 당신은 날짜 범위에있는 모든 요일을 계산 할 수 있습니다.

    여기 빠르고 유연 함수이다. 당신은 날짜 범위에있는 모든 요일을 계산 할 수 있습니다.

    CREATE OR REPLACE FUNCTION wfportal.cx_count_specific_weekdays( p_week_days   VARCHAR2 DEFAULT 'MON,TUE,WED,THU,FRI'
                                                              , p_start_date  DATE
                                                              , p_end_date    DATE)
RETURN NUMBER 
IS

 /***************************************************************************************************************
  *
  * FUNCTION DESCRIPTION:
  *
  *   This function calculates the total required week days in a date range.
  *
  * PARAMETERS:
  *
  *   p_week_days   VARCHAR2  The week days that need to be counted, comma seperated e.g. MON,TUE,WED,THU,FRU,SAT,SUN 
  *   p_start_date  DATE      The start date
  *   p_end_date    DATE      The end date
  *
  * CHANGE history
  *
  * No.  Date         Changed by       Change Description
  * ---- -----------  -------------    -------------------------------------------------------------------------
  *    0 07-May-2013  yourname         Created
  *
  ***************************************************************************************************************/

   v_date_end_first_date_range    DATE;
   v_date_start_last_date_range   DATE;
   v_total_days_in_the_weeks      NUMBER;
   v_total_days_first_date_range  NUMBER;
   v_total_days_last_date_range   NUMBER;
   v_output                       NUMBER;

   v_error_text                   CX_ERROR_CODES.ERROR_MESSAGE%TYPE;

   --Count the required days in a specific date ranges by using a list of all the weekdays in that range.
   CURSOR c_total_days ( v_start_date DATE
                       , v_end_date   DATE ) IS
     SELECT COUNT(*) total_days
     FROM ( SELECT ( v_start_date + level - 1) days
            FROM dual
            CONNECT BY LEVEL <= ( v_end_date - v_start_date ) + 1
           )
     WHERE INSTR( ',' || p_week_days || ',', ',' || TO_CHAR( days, 'DY', 'NLS_DATE_LANGUAGE=english') || ',', 1 ) > 0
   ;

   --Calculate the first and last date range by retrieving the first Sunday after the start date and the last Monday before the end date. 
   --Calculate the total amount of weeks in between and multiply that with the total required days.
   CURSOR c_calculate_new_dates ( v_start_date DATE
                                , v_end_date   DATE ) IS
     SELECT date_end_first_date_range
     ,      date_start_last_date_range
     ,      ( 
              (
                ( date_start_last_date_range - ( date_end_first_date_range + 1 ) )
              ) / 7 
            ) * total_required_days   total_days_in_the_weeks  --The total amount of required days 
     FROM ( SELECT v_start_date + DECODE( TO_CHAR( v_start_date, 'DY', 'NLS_DATE_LANGUAGE=english')
                                        , 'MON', 6
                                        , 'TUE', 5
                                        , 'WED', 4
                                        , 'THU', 3
                                        , 'FRI', 2
                                        , 'SAT', 1
                                        , 'SUN', 0
                                        , 0 )   date_end_first_date_range
            ,      v_end_date - DECODE( TO_CHAR( v_end_date, 'DY', 'NLS_DATE_LANGUAGE=english')
                                      , 'MON', 0
                                      , 'TUE', 1
                                      , 'WED', 2
                                      , 'THU', 3
                                      , 'FRI', 4
                                      , 'SAT', 5
                                      , 'SUN', 6
                                      , 0 )  date_start_last_date_range
            ,      REGEXP_COUNT( p_week_days, ',' ) + 1  total_required_days  --Count the commas + 1 to get the total required weekdays
            FROM dual 
     )
     ;

BEGIN

  --Verify that the start date is before the end date
  IF p_start_date < p_end_date THEN

    --Get the new calculated days.
    OPEN c_calculate_new_dates( p_start_date, p_end_date );

      FETCH c_calculate_new_dates INTO  v_date_end_first_date_range
                                      , v_date_start_last_date_range
                                      , v_total_days_in_the_weeks;

    CLOSE c_calculate_new_dates;

    --Calculate the days in the first date range
    OPEN c_total_days( p_start_date, v_date_end_first_date_range );
      FETCH c_total_days INTO v_total_days_first_date_range;
    CLOSE c_total_days;

    --Calculate the days in the last date range
    OPEN c_total_days( v_date_start_last_date_range, p_end_date );
      FETCH c_total_days INTO v_total_days_last_date_range;
    CLOSE c_total_days;

    --Sum the total required days
    v_output := v_total_days_first_date_range + v_total_days_last_date_range + v_total_days_in_the_weeks;

  ELSE

     v_output := 0;

  END IF;

  RETURN v_output;

  EXCEPTION

    WHEN OTHERS
    THEN

    RETURN NULL;

END cx_count_specific_weekdays;
/
  10. ==============================

    10.여기 요 ...

    여기 요 ...

    그 후 당신은 그것을처럼 테스트 할 수 있습니다 :

        select 
            calculate_business_days('21-AUG-2013','28-AUG-2013') as business_days 
    from dual;
  11. ==============================

    11.또 다른 쉬운 방법은 연결하여 듀얼을 사용하여,이 ...

    또 다른 쉬운 방법은 연결하여 듀얼을 사용하여,이 ...

    with t as (select to_date('30-sep-2013') end_date, trunc(sysdate) start_date from dual)select count(1) from dual, t where to_char(t.start_date  + level, 'D') not in (1,7) connect by t.start_date + level <= t.end_date;

    당신에 의해 연결과 함께하면 종료일까지 시작일부터 모든 날짜를 얻을. 그럼 당신은 당신이 필요로하는 만 필요에 포함되지 않습니다 날짜를 제외 할 수 있습니다.

  12. ==============================

    12.이 일을 반환합니다 :

    이 일을 반환합니다 :

    (CompleteDate-InstallDate)-2*FLOOR((CompleteDate-InstallDate)/7)-
  DECODE(SIGN(TO_CHAR(CompleteDate,'D')-
    TO_CHAR(InstallDate,'D')),-1,2,0)+DECODE(TO_CHAR(CompleteDate,'D'),7,1,0)-
    DECODE(TO_CHAR(InstallDate,'D'),7,1,0) as BusinessDays,

    13. from https://stackoverflow.com/questions/14898357/calculate-business-days-in-oracle-sqlno-functions-or-procedure by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버 UNION - 행동 BY 기본 ORDER 무엇  (0) 2020.04.21
[SQL] SELECT *를 사용에서 성능 문제? [복제]  (0) 2020.04.21
[SQL] RODBC와 매개 변수가있는 쿼리  (0) 2020.04.21
[SQL] 사용 A는 WHERE 조건에 비해 절을 가입하는 이유?  (0) 2020.04.21
[SQL] SQL Server 2005의 흥미로운 행동 "테이블에서 등 COLA, COLB을 선택"VS "테이블에서 선택 *"  (0) 2020.04.21
,

티스토리툴바