mes - Calcular días hábiles en Oracle SQL(sin funciones ni procedimientos)

dias laborables entre dos fechas tableau (11)

Cambié mi ejemplo a más legible y para devolver el conteo de bus. días entre. No sé por qué necesitas el formato ''J''-Julian. Todo lo que necesita es iniciar / instalar y finalizar / completar fechas. Obtendrá el número correcto de días entre 2 fechas usando esto. Reemplace mis fechas con las suyas, agregue 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

Estoy tratando de calcular los días hábiles entre dos fechas en Oracle select. Llegué al punto en que mi cálculo da la mayoría de los resultados correctos para las fechas dadas (lo comparo con DÍAS DE LA RED 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!

Prueba 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í hay una demo de sqlfiddle

Aquí hay una función que es rápida y flexible. Puede contar cualquier día de la semana en un rango de fechas.

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; /

Aqui tienes...

Primero compruebe cuántos días recibió en la tabla de vacaciones, excluyendo los días de fin de semana.
Obtenga días hábiles (LUN a VIERNES) entre las 2 fechas y luego reste los días festivos.
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 eso, puedes probarlo, como:

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

Hay otra manera más fácil, usando connect by y dual ...

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 connect by obtendrá todas las fechas desde start_date hasta end_date. Luego puede excluir las fechas que no necesita y contar solo las que necesita.

Tomé en cuenta todos los diferentes enfoques discutidos anteriormente y surgió una consulta simple que nos da el número de días hábiles 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.

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 tus respuestas !

La solución aceptada es bastante aproximada, pero parece equivocada en algunos casos (por ej., Del 2/1/2015 al 2-28 / 2015 o del 5/1/2015 al 5/31/2015). Aquí hay 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 maneja los múltiplos de 7 (semanas enteras) es buena. Pero, al considerar la porción de la semana parcial, depende de la compensación del día de la semana y del número de días en la porción parcial, de acuerdo con la siguiente matriz ...

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

Veo que la solución final marcada no siempre es correcta. Supongamos que InstallDate es el 1 ° del mes (si cae el sábado) y CompleteDate es el 16 del mes (si cae el domingo)

En ese caso, los Días Hábiles reales son 10, pero el resultado de la consulta marcada dará la respuesta como 12. Entonces, tenemos que tratar este tipo de casos también, que utilicé

(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 para manejarlo.

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;

Para eliminar los domingos y los sábados puedes usar esto

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 sabados

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

Esta consulta se puede utilizar para retroceder N días a partir de la fecha indicada (días hábiles solamente)

Por ejemplo, retroceda 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 retroceder N días a partir de una fecha determinada. Busca el sábado más cercano que sea menor o igual a la fecha especificada. - Desde el sábado más cercano, regrese días (N - desplazamiento). La compensación es el número de días hábiles entre el sábado más cercano y la fecha determinada (excluida la fecha indicada).

* Para volver M días desde un sábado (días hábiles solamente), utilice esta fórmula Fecha de vencimiento del sábado - [M + Piso (M / 6) * 2]