Estoy tratando de calcular los días hábiles entre dos fechas en Oracle seleccione. He llegado al punto de cuando mi cálculo da más resultados correctos para las fechas (yo lo comparo con NETWORKDAYS en excel), pero a veces varía de 2 días a -2 días – y no sé por qué…

Aquí está mi código:

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

Gracias!

Agregar la solución como una respuesta, usted puede «aceptar» (es mejor para que este sitio tiene preguntas con respuestas aceptadas)

OriginalEl autor yochim | 2013-02-15

12 Comentarios

  1. 22

    La solución, finalmente:

    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;

    Gracias por todas sus respuestas !

    Creo que estás tomando un buen enfoque, tratando de utilizar SQL simples funciones en lugar de una función o de una tabla de fechas. Pero los resultados no son siempre exactos. Por ejemplo, si utiliza las fechas 2012-02-15 y 2012-02-18, un viernes y el lunes, el resultado es 3.
    Bueno, en realidad no funciona correctamente para todas las fechas 🙂 Que sólo se utiliza mal las fechas 🙂 2012-02-15 es miércoles, y 2012-02-18 es el sábado. Supongo que quieres calcular mismos días, pero en 2013, da a 2 días (de nuevo!).
    +1 Woops, lo siento.
    He tenido que añadir en dos casos adicionales para que esto funcione para mí. Nota: de acuerdo a mi entorno de oracle, una semana empieza en lunes. No estoy seguro si esto es universal para oracle o no.

    OriginalEl autor yochim

  2. 5

    Tomé en cuenta todos los diferentes enfoques discutidos anteriormente y vino para arriba con una simple consulta que nos da el número de días de trabajo en cada mes del año, entre dos fechas:

    WITH test_data AS
    (
    SELECT TO_DATE('01-JAN-14') AS start_date,
    TO_DATE('31-DEC-14') AS end_date
    FROM dual
    ),
    all_dates AS
    (
    SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day
    FROM test_data td
    CONNECT BY td.start_date + LEVEL-1 <= td.end_date)
    SELECT TO_CHAR(week_day, 'MON'), COUNT(*)
    FROM all_dates
    WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat')
    GROUP BY TO_CHAR(week_day, 'MON');

    Por favor, siéntase libre de modificar la consulta según sea necesario.

    Muchas gracias por esta consulta. La he modificado para reducir el número de días laborables por nuestras vacaciones de la empresa, y ahora soy capaz de utilizar este cálculo solo.

    OriginalEl autor OraGeek

  3. 1

    Intente esto:

    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

    Aquí está una sqlfiddle demo

    Gracias, yo tengo que trabajar con mi enfoque 🙂

    OriginalEl autor A.B.Cade

  4. 1

    Veo que marcaron la solución final no es correcto siempre. Supongamos, fecha de instalación, es el 1 del mes (si cae en sábado) y CompleteDate es 16 del mes (si cae en domingo)

    En ese caso, la Empresa real Días es de 10, pero la marcada resultado de la consulta va a dar la respuesta como 12. Así, tenemos que tratar este tipo de casos, la cual he usado

    (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

    línea de manejar.

    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;

    OriginalEl autor Hiran

  5. 0

    He cambiado mi ejemplo para que sea más legible y a devolver la cuenta de autobús. días entre. No sé por qué usted necesita ‘J’- formato Juliano. Todo lo que toma es de inicio/Instalar y final Completo de fechas. Usted va a obtener el número correcto de días entre 2 fechas el uso de este. Reemplazar mis fechas con la suya, agregar NLS si es necesario…:

     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
    Gracias – yo era capaz de calcular con mi método
    compártelo con nosotros.
    Se comparte como una respuesta a mi post original
    He intentado utilizar su método y tengo 2 en volver… tal vez yo no estoy entendiendo o algo… Aquí está la consulta-por favor, copiar y pegar, si les interesa: SELECCIONE TRUNC((CompleteDate),’J’) CompleteDate , TRUNC((fecha de instalación),’J’) fecha de instalación , ((((TRUNC((CompleteDate),’D’))-(TRUNC((fecha de instalación),’D’)))/7)*2) «wks?» ( SELECCIONE TO_DATE(‘2013-02-16’, ‘AAAA-MM-DD’) fecha de instalación , TO_DATE(‘2013-02-23’, ‘AAAA-MM-DD’) CompleteDate DE dual ) /
    No sé lo que estás tratando de calcular con tu consulta 🙂 Si me pega su fechas en mi consulta tengo 5 días de SELECCIONAR (TO_CHAR(TO_DATE(‘2013-02-23′,’AAAA-MM-DD’),’J’) – TO_CHAR(TO_DATE(‘2013-02-16′,’AAAA-MM-DD’),’J’))+1 – ((((TRUNC(TO_DATE(‘2013-02-23′,’YYYY-MM-DD’),’D’))-(TRUNC(TO_DATE(‘2013-02-16′,’YYYY-MM-DD’),’D’)))/7)*2) – (CASO CUANDO TO_CHAR(TO_DATE(‘2013-02-16′,’AAAA-MM-DD’),’DY’,’nls_date_language=english’)=’SOL’ THEN 1 ELSE 0 END) -(CASO CUANDO TO_CHAR(TO_DATE(‘2013-02-23′,’AAAA-MM-DD’),’DY’,’nls_date_language=english’)=’SAT’ THEN 1 ELSE 0 END) como días laborables DESDE la DOBLE

    OriginalEl autor Art

  6. 0

    La aceptación de la solución está muy cerca, pero parece equivocado en algunos casos (por ejemplo, 2/1/2015 a través de 2-28/2015 o 5/1/2015 a través de 5/31/2015). He aquí una versión refinada…

      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

    La parte que se encarga de los múltiplos de 7 (semanas completas) es buena. Pero, cuando se considera la semana parcial parte, depende de que tanto el día de la semana de desplazamiento y el número de días en el parcial de parte, de acuerdo a la siguiente matriz…

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

    OriginalEl autor deanashuff

  7. 0

    Para eliminar sólo los domingos y los sábados se puede utilizar este

    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 cuenta el número de días entre las dos fechas

    (floor((Base_DateDiff + 0 + Start_WeekDay) /7)) cuenta el número de domingos

    (floor((Base_DateDiff + 1 + Start_WeekDay) /7)) cuenta el número de sábados

    1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW') obtenga 1 para el lunes a las 7 para el domingo

    OriginalEl autor Serpiton

  8. 0

    Esta consulta se puede utilizar para ir hacia atrás N días a partir de la fecha dada (únicamente los días hábiles)

    Por ejemplo, ir hacia atrás de 15 días a partir de 2017-05-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
              ))

    Una breve explicación: supongamos que queremos ir hacia atrás N días a partir de una fecha dada
    – Encontrar el sábado más próximo que es menor que o igual a la fecha dada.
    – Desde el sábado más próximo, volver ward (N – offset) días. desplazamiento es el número de días hábiles entre el sábado más próximo y la fecha dada (excluyendo la fecha dada).

    *Para volver M días de un sábado (únicamente los días hábiles), el uso de esta fórmula DateOfMonthOfTheSaturday – [M + Suelo(M /6) * 2]

    OriginalEl autor beckham12a18

  9. -1

    Aquí es una función que es rápido y flexible. Usted puede contar cualquier día de la semana en un rango de fecha.

    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;
    /
    El autor de la pregunta solicitada ninguna las funciones o procedimientos. Este es probablemente por qué su respuesta fue abajo-votaron a favor.

    OriginalEl autor Stephen

  10. -1

    Aquí la tienes…

    1. Primera comprobar cuántos días tienes en la tabla de vacaciones, excluyendo los días de fin de semana.
    2. Obtener días hábiles (de LUNES a VIERNES) entre las 2 fechas y después de que restar los días de vacaciones.

      create or replace
      FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE)
      RETURN NUMBER IS
      v_holidays     NUMBER;
      v_start_date   DATE   := TRUNC (p_start_date);
      v_end_date     DATE   := TRUNC (p_end_date);
      BEGIN
      IF v_end_date >= v_start_date
      THEN
      SELECT COUNT (*)
      INTO v_holidays
      FROM holidays
      WHERE day BETWEEN v_start_date AND v_end_date
      AND day NOT IN (
      SELECT hol.day 
      FROM holidays hol 
      WHERE MOD(TO_CHAR(hol.day, 'J'), 7) + 1 IN (6, 7)
      );
      RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
      +   (  (  NEXT_DAY (v_end_date, 'MON')
      - NEXT_DAY (v_start_date, 'MON')
      )
      / 7
      )
      * 5
      - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
      - v_holidays;
      ELSE
      RETURN NULL;
      END IF;
      END calculate_business_days;

    Después de que usted puede probarlo, como:

        select 
    calculate_business_days('21-AUG-2013','28-AUG-2013') as business_days 
    from dual;
    El autor de la pregunta solicitada ninguna las funciones o procedimientos. Este es probablemente por qué su respuesta fue abajo-votaron a favor.

    OriginalEl autor MChristiaan

  11. -1

    Hay otro camino más fácil, el uso de conectarse y doble…

    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;

    con conectar por usted obtiene todas las fechas de start_date hasta el end_date. A continuación, puede excluir las fechas que usted no necesita y cuenta solamente la necesaria.

    OriginalEl autor Nikos Giannios

  12. -1

    Esto volvería días laborales:

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

    OriginalEl autor Reza Rahimi

Dejar respuesta

Please enter your comment!
Please enter your name here