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 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]