sumar - sql funcion dias habiles
Función MySQL para encontrar el número de días hábiles entre dos fechas (29)
¿Podrían las cuerdas propuestas ser incorrectas?
DATEDIFF (de, a) excluye ''a''. De la misma manera también debería esta cadena:
Lunes -> viernes = {lun, tu, mié, th} = 4
Lunes -> Sábado = {Lunes, Miércoles, Miércoles, Jueves, Viernes} = 5
Martes -> Lunes = {Tu, Mie, Jue, Vie, sáltate Sáb, omite Sol, Mon está excluido} = 4
y así
Matriz propuesta:
| M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 4 0 1 2 3 4 4
W| 3 4 0 1 2 3 3
T| 2 3 4 0 1 2 2
F| 1 2 3 4 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0
Cadena: ''0123455401234434012332340122123401101234000123450''
¿Me estoy perdiendo de algo? :)
Excel tiene la función NETWORKDAYS () que determina el número de días hábiles entre dos fechas.
¿Alguien tiene una función similar para MySQL? Dado que las vacaciones añaden complejidad, la solución no tiene que lidiar con las vacaciones.
Aunque es un mensaje VIEJO pero ayuda mucho. Según la solución proporcionada por @shahcool, no está devolviendo días exactos, por ejemplo
Workdays(''2013-03-26'',''2013-04-01'') Devuelve 3 días pero en realidad debe ser 5 días
A continuación se muestra la solución que he probado y Retruntar días laborables exactos
DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYS $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE NumberOfWeeks INT;
DECLARE RemainingDays INT;
DECLARE firstDayOfTheWeek INT;
DECLARE lastDayOfTheWeek INT;
DECLARE WorkingDays INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
## Add one to include both days in interval
SET diff = DATEDIFF(end_date, start_date)+1;
SET NumberOfWeeks=floor(diff/7);
SET RemainingDays=MOD(diff,7);
SET firstDayOfTheWeek=DAYOFWEEK(start_date);
SET lastDayOfTheWeek=DAYOFWEEK(end_date);
IF(firstDayOfTheWeek <= lastDayOfTheWeek) THEN
IF( firstDayOfTheWeek<=6 AND 6 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
IF( firstDayOfTheWeek<=7 AND 7 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
ELSE
IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
IF (lastDayOfTheWeek=6) THEN SET RemainingDays=RemainingDays-1; END IF;
ELSE SET RemainingDays=RemainingDays-2;
END IF;
END IF;
SET WorkingDays=NumberOfWeeks*5;
IF(RemainingDays>0) THEN RETURN WorkingDays+RemainingDays;
ELSE RETURN WorkingDays; END IF;
END $$
DELIMITER ;
Como necesitará realizar un seguimiento de las vacaciones en algún lugar, una tabla de Calendario parece apropiada:
CREATE TABLE Calendar
(
calendar_date DATETIME NOT NULL,
is_holiday BIT NOT NULL,
is_weekend BIT NOT NULL,
CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)
Por supuesto, debe llenarlo con todas las fechas para cualquier período de tiempo que pueda trabajar en su aplicación. Como solo hay 365 (o 366) días en un año, pasar de 1900 a 2100 no es un gran problema. Solo asegúrate de cargarlo con todas las fechas, no solo las vacaciones.
En ese momento, las consultas como la que necesita se vuelven triviales:
SELECT
COUNT(*)
FROM
Calendar
WHERE
calendar_date BETWEEN ''2009-01-01'' AND ''2009-10-01'' AND
is_holiday = 0 AND
is_weekend = 0
Advertencia: trabajo principalmente con MS SQL y no he trabajado con MySQL en mucho tiempo, por lo que es posible que tengas que modificar lo anterior. Por ejemplo, ni siquiera recuerdo si MySQL tiene el tipo de datos BIT.
Dado el primer día de un mes, esto devolverá el número de días laborables dentro de ese mes. En MySQL Sin un procedimiento almacenado.
SELECT (DATEDIFF(LAST_DAY(?),?) + 1) -
((WEEK(LAST_DAY(?)) - WEEK(?)) * 2) -
(case when weekday(?) = 6 then 1 else 0 end) -
(case when weekday(LAST_DAY(?)) = 5 then 1 else 0 end)
El problema que tendrá con el par "ignorar las vacaciones" es que cada país tendrá vacaciones diferentes.
Deberá comenzar por definir las vacaciones de su país y luego pasarlas para ver si una fecha determinada es feriado.
No sé de funciones genéricas que hagan lo que quieras en mysql
¡Lo siento!
En función de la función anterior de Yada, aquí hay una ligera variación del tema, que calcula los días de trabajo restantes desde la fecha actual (sin incluir) hasta la fecha objetivo. También maneja los diferentes días de fin de semana en Israel :-) Tenga en cuenta que esto producirá un resultado negativo si la fecha objetivo está en el pasado (que es justo lo que quería).
DELIMITER //
DROP FUNCTION IF EXISTS WORKDAYS_LEFT//
CREATE FUNCTION WORKDAYS_LEFT(target_date DATE, location char(2))
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE check_date DATE;
DECLARE diff INT;
DECLARE extra_weekend_days INT;
DECLARE weeks_diff INT;
SET start_date = CURDATE();
SET end_date = target_date;
SET diff = DATEDIFF(end_date, start_date);
SET weeks_diff = FLOOR(diff / 7);
SET end_date = DATE_SUB(end_date, INTERVAL (weeks_diff * 7) DAY);
SET check_date = DATE_ADD(start_date, INTERVAL 1 DAY);
SET extra_weekend_days = 0;
WHILE check_date <= end_date DO
SET extra_weekend_days = extra_weekend_days +
IF(DAYNAME(check_date) = ''Saturday'', 1, 0) +
IF(DAYNAME(check_date) = IF(location = ''IL'',''Friday'', ''Sunday''), 1, 0);
SET check_date = DATE_ADD(check_date, INTERVAL 1 DAY);
END WHILE;
RETURN diff - weeks_diff*2 - extra_weekend_days;
END//
DELIMITER ;
Esta consulta devuelve fácilmente el número de días hábiles entre dos fechas que excluyen los fines de semana:
select datediff(''2016-06-19'',''2016-06-01'') - (floor(datediff(''2016-06-19'',''2016-06-01'')/6) + floor(datediff(''2016-06-19'',''2016-06-01'')/7));
Esta es una caída en reemplazo de DATEDIFF que funciona para las diferencias + ve y -ve.
DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYSDIFF$$
CREATE FUNCTION WORKDAYSDIFF(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
RETURN IF (sd >= ed,
5 * (DATEDIFF(sd, ed) DIV 7) + MID(''0123455501234445012333450122234501101234000123450'', 7 * WEEKDAY(ed) + WEEKDAY(sd) + 1, 1),
-(5 * (DATEDIFF(ed, sd) DIV 7) + MID(''0123455501234445012333450122234501101234000123450'', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1)) );
END$$
DELIMITER ;
Esta expresión -
5 * (DATEDIFF(@E, @S) DIV 7) + MID(''0123444401233334012222340111123400012345001234550'', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
calcula el número de días hábiles entre la fecha de inicio @S y la fecha de finalización @E.
Asume que la fecha de finalización (@E) no es anterior a la fecha de inicio (@S). Compatible con DATEDIFF en que la misma fecha de inicio y fecha de finalización le da cero días hábiles. Ignora las vacaciones.
La cadena de dígitos se construye de la siguiente manera. Cree una tabla de días de inicio y días de finalización, las filas deben comenzar el lunes (DÍA DE LA SEMANA 0) y las columnas deben comenzar también el lunes. Complete la diagonal desde la esquina superior izquierda a la esquina inferior derecha con todos los 0 (es decir, hay 0 días hábiles entre lunes y lunes, martes y martes, etc.). Para cada día, comience por la diagonal (siempre debe ser 0) y complete las columnas a la derecha, un día a la vez. Si aterriza en una columna de día de fin de semana (día no laborable), el número de días hábiles no cambia, se transfiere desde la izquierda. De lo contrario, el número de días hábiles aumenta en uno. Cuando llegue al final del ciclo de la fila de nuevo al comienzo de la misma fila y continúe hasta llegar a la diagonal de nuevo. Luego pasa a la siguiente fila.
Ej. Suponer que el sábado y el domingo no son días hábiles,
| M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0
Luego concatenar los 49 valores de la tabla en la cadena.
Por favor, avíseme si encuentra algún error.
-Editar tabla mejorada:
| M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0
cadena mejorada: ''0123444401233334012222340111123400001234000123440''
expresión mejorada:
5 * (DATEDIFF(@E, @S) DIV 7) + MID(''0123444401233334012222340111123400001234000123440'', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
Esta solución utiliza básicamente el mismo enfoque que la de Rodger, excepto que el método para generar la matriz es mucho más complejo. Nota: Esta salida de esta solución no es compatible con NETWORKDAYS.
Al igual que en la solución de Rodger, esto calcula el número de días hábiles entre la fecha de inicio (@S) y la fecha de finalización (@E) sin tener que definir un procedimiento almacenado. Supone que la fecha de finalización no es anterior a la fecha de inicio. Usar la misma fecha de inicio y final producirá 0. Las vacaciones no se tienen en cuenta.
La principal diferencia entre esto y la solución de Rodger es que la matriz y la cadena resultante de dígitos está construida por un complejo algoritmo que no he incluido. La salida de este algoritmo se valida mediante una prueba unitaria (ver las entradas y salidas de prueba a continuación). En la matriz, la intersección de cualquier par de valores xyy (WEEKDAY (@S) y WEEKDAY (@E) arroja la diferencia en días de trabajo entre los dos valores. El orden de asignación no es importante ya que los dos se suman para trazar la posición.
Los días hábiles son de lunes a viernes
| M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0
Los 49 valores de la tabla se concatenan en la siguiente cadena:
0123455501234445012333450122234501101234000123450
Al final, la expresión correcta es:
5 * (DATEDIFF(@E, @S) DIV 7) + MID(''0123455501234445012333450122234501101234000123450'', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
He verificado las siguientes entradas y salidas usando esta solución:
Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5
Esto funciona en Sql Server 2005
No sé si te va a funcionar.
DECLARE @StartDate DATETIME,
@EndDate DATETIME
SELECT @StartDate = ''22 Nov 2009'',
@EndDate = ''28 Nov 2009''
;WITH CTE AS(
SELECT @StartDate DateVal,
DATENAME(dw, @StartDate) DayNameVal
UNION ALL
SELECT DateVal + 1,
DATENAME(dw, DateVal + 1)
FROM CTE
WHERE DateVal < @EndDate
)
SELECT COUNT(1)
FROM (
SELECT *
FROM CTE
WHERE DayNameVal NOT IN (''Sunday'',''Saturday'')
) DayVals
Función MYSQL que devuelve días hábiles entre 2 fechas (inclusive). BETWEEN 2 AND 6 es de lunes a viernes, esto se puede ajustar según su calendario / región.
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetBusinessDaysBetweenDates`(d1 DATE, d2 DATE) RETURNS int(11)
BEGIN
DECLARE bDaysInPeriod INT;
SET bDaysInPeriod=0;
WHILE d1<=d2 DO
IF DAYOFWEEK(d1) BETWEEN 2 AND 6 THEN
SET bDaysInPeriod=bDaysInPeriod+1;
END IF;
SET d1=d1+INTERVAL 1 day;
END WHILE;
RETURN bDaysInPeriod;
END
Helooo prueba por favor.
DELIMITER $$
DROP FUNCTION IF EXISTS `WORKDAYS` $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE cnt INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
SELECT COUNT(*) INTO cnt FROM `holiday` WHERE (hday BETWEEN start_date AND end_date) and (DAYOFWEEK(hday) != 7 and DAYOFWEEK(hday) != 1);
SET diff = DATEDIFF(end_date, start_date) ;
RETURN (CASE WHEN DAYNAME(start_date) not in (''Saturday'', ''Sunday'') && DAYNAME(end_date) = ''Saturday'' THEN (diff - cnt)
WHEN DAYNAME(start_date) not in (''Saturday'', ''Sunday'') && DAYNAME(end_date) = ''Sunday'' THEN (diff - 2 - cnt)
WHEN DAYNAME(start_date) = ''Saturday'' && DAYNAME(end_date) = ''Sunday'' THEN (diff - 1 - cnt)
WHEN DAYNAME(start_date) = ''Saturday'' && DAYNAME(end_date) = ''Saturday'' THEN (diff + 1 - cnt)
WHEN DAYNAME(start_date) = ''Sunday'' && DAYNAME(end_date) in (''Saturday'', ''Sunday'') THEN (diff + 1 - cnt)
WHEN DAYNAME(start_date) = ''Saturday'' && DAYNAME(end_date) not in (''Saturday'', ''Sunday'') THEN (diff -1 - cnt)
WHEN DAYNAME(start_date) = ''Sunday'' && DAYNAME(end_date) not in (''Saturday'', ''Sunday'') THEN (diff + 1 - cnt)
WHEN DAYNAME(start_date) not in (''Saturday'', ''Sunday'') && DAYNAME(end_date) not in (''Saturday'', ''Sunday'')
&& WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2 - cnt)
ELSE (diff - cnt) END)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = ''Sunday'' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = ''Saturday'' THEN 1 ELSE 0 END);
END $$
y vacaciones de mesa
DROP TABLE IF EXISTS `holiday`;
CREATE TABLE `holiday` (
`id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,
`hday` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `holiday` (`id`, `hday`) VALUES
(1, ''2012-01-01''),
(2, ''2012-05-01''),
(3, ''2012-05-08''),
(4, ''2012-07-05''),
(5, ''2012-07-06''),
(6, ''2012-09-28''),
(7, ''2012-10-28''),
(8, ''2012-11-17''),
(9, ''2012-12-24''),
(10, ''2012-12-25''),
(11, ''2012-12-26'');
etc...
La diferencia de días sin fines de semana se puede lograr de esta manera:
CREATE FUNCTION `WDDIFF` (d0 DATE, d1 DATE)
RETURNS INT DETERMINISTIC
COMMENT ''Date0, Date1''
BEGIN
RETURN DATEDIFF(d1, d0) - (DATEDIFF(DATE_SUB(d1, INTERVAL WEEKDAY(d1) DAY), DATE_ADD(d0, INTERVAL (7 - WEEKDAY(d0)) DAY))/7+1)*2 + IF(WEEKDAY(d0)>4, 1, 0) + 1;
END
Uso: días de la semana desde el comienzo del mes
SELECT ap.WDDIFF(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY), CURDATE())
Nota: La función cuenta las fechas de inicio y finalización
La respuesta principal se contabilizó para los días comprendidos entre la fecha de inicio y la fecha de finalización pero excluyó la fecha de finalización.
También para las fechas que comenzaron y terminan en los mismos días de fin de semana, por ejemplo, el sábado 2018-05-05 al sábado 2018-05-12, se calculó un día más.
¡Aquí hay una función que funciona perfectamente para mí!
drop procedure if exists get_duration$$
create procedure get_duration(in data_from date, in data_to date)
begin
if (WEEKDAY(data_from) = 5 AND WEEKDAY(data_to) = 5)
OR (WEEKDAY(data_from) = 6 AND WEEKDAY(data_to) = 6) then
select (5 * (DATEDIFF(data_to, data_from) DIV 7)
+ MID(''0123444401233334012222340111123400001234000123440'',
7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1)) dur;
else
select (5 * (DATEDIFF(data_to, data_from) DIV 7)
+ MID(''0123444401233334012222340111123400001234000123440'',
7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1))+1 dur;
end if;
end$$
La solución de Yada no funciona correctamente. Mis cambios:
DELIMITER $$
DROP FUNCTION IF EXISTS `catalog`.`WORKDAYS` $$
CREATE FUNCTION `catalog`.`WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
SET diff = DATEDIFF(end_date, start_date);
RETURN (CASE WHEN DAYNAME(start_date) not in (''Saturday'', ''Sunday'') && DAYNAME(end_date) = ''Saturday'' THEN diff
WHEN DAYNAME(start_date) not in (''Saturday'', ''Sunday'') && DAYNAME(end_date) = ''Sunday'' THEN (diff - 2)
WHEN DAYNAME(start_date) = ''Saturday'' && DAYNAME(end_date) = ''Sunday'' THEN (diff - 1)
WHEN DAYNAME(start_date) = ''Saturday'' && DAYNAME(end_date) = ''Saturday'' THEN (diff + 1)
WHEN DAYNAME(start_date) = ''Sunday'' && DAYNAME(end_date) in (''Saturday'', ''Sunday'') THEN (diff + 1)
WHEN DAYNAME(start_date) = ''Saturday'' && DAYNAME(end_date) not in (''Saturday'', ''Sunday'') THEN (diff -1)
WHEN DAYNAME(start_date) = ''Sunday'' && DAYNAME(end_date) not in (''Saturday'', ''Sunday'') THEN (diff + 1)
WHEN DAYNAME(start_date) not in (''Saturday'', ''Sunday'') && DAYNAME(end_date) not in (''Saturday'', ''Sunday'')
&& WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2)
ELSE diff END)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = ''Sunday'' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = ''Saturday'' THEN 1 ELSE 0 END);
END $$
DELIMITER ;
Necesitaba dos funciones. Uno para calcular el número de días hábiles entre dos fechas y una para sumar / restar x días hábiles a una fecha. Esto es lo que reuní de los ejemplos que encontré en Internet. Están diseñados para estar cerca de las funciones estándar DATEDIFF () y DATE_ADD (), así como para complementar los cálculos de los demás. Por ejemplo, DateDiffBusiness (''2014-05-14'', DateAddBusiness (''2014-05-14'', 5)) será igual a 5.
DROP FUNCTION IF EXISTS DateDiffBusiness;
DELIMITER &
CREATE FUNCTION DateDiffBusiness( d2 DATE, d1 DATE )
RETURNS INT
DETERMINISTIC
COMMENT ''Calculates the number of bussiness days between two dates''
BEGIN
DECLARE dow1, dow2, days INT;
SET dow1 = DAYOFWEEK(d1);
SET dow2 = DAYOFWEEK(d2);
SET days = FLOOR( DATEDIFF(d2,d1)/7 ) * 5 +
CASE
WHEN dow1=1 AND dow2=7 THEN 5
WHEN dow1 IN(7,1) AND dow2 IN (7,1) THEN 0
WHEN dow1=dow2 THEN 1
WHEN dow1 IN(7,1) AND dow2 NOT IN (7,1) THEN dow2-1
WHEN dow1 NOT IN(7,1) AND dow2 IN(7,1) THEN 7-dow1
WHEN dow1<=dow2 THEN dow2-dow1+1
WHEN dow1>dow2 THEN 5-(dow1-dow2-1)
ELSE 0
END;
RETURN days-1;
END&
DELIMITER ;
DROP FUNCTION IF EXISTS DateAddBusiness;
DELIMITER &
CREATE FUNCTION DateAddBusiness(mydate DATE, numday INT)
RETURNS DATE
DETERMINISTIC
COMMENT ''Adds bussiness days between two dates''
BEGIN
DECLARE num_week INT DEFAULT 0;
DECLARE num_day INT DEFAULT 0;
DECLARE adj INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
SET num_week = numday DIV 5;
SET num_day = MOD(numday, 5);
IF (WEEKDAY(mydate) + num_day >= 5) then
SET adj = 2;
END IF;
SET total = num_week * 7 + adj + num_day;
RETURN DATE_ADD(mydate, INTERVAL total DAY);
END&
DELIMITER ;
Para la función NETWORKDAYS () anterior, se debe agregar una condición más para cubrir casos cuando la fecha de inicio a la fecha de finalización es dentro de los 7 días y durante un fin de semana.
RETURN (diff + 1)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = ''Sunday'' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = ''Saturday'' THEN 1 ELSE 0 END)
- (CASE WHEN diff<7 and WEEK(start_date)<>WEEK(end_date) THEN 2 ELSE 0 end);
Respuesta publicada por @Rodger Bagnall no funciona correctamente para mí, por ejemplo, en 2016-04. Muestra 1 día menos que en real.
si hablamos de calcular por consulta, uso esto:
set
@S = ''2016-04-01'',
@E = ''2016-04-30'';
select
case
when WEEKDAY(@S) < 5 then 5 - WEEKDAY(@S)
else 0
end #startweek
+
case
when WEEKDAY(@E) < 5 then WEEKDAY(@E) + 1
else 5
end #endweek
+
(
DATEDIFF(@E, @S) + 1 # plus 1 day cause params is inside 1 month
- (7 - WEEKDAY(@S)) # minus start week
- (WEEKDAY(@E) + 1) # minus end week
) DIV 7 * 5 #rest part
as work_date_count;
Consulta no optimizada solo para mostrar de dónde vienen los números
Sé que este es un hilo viejo, pero pensaba que mi solución podría ser útil para algunas personas. esta es una consulta que hice para encontrar los días de trabajo sin la necesidad de funciones. puede asignarle el nombre que desee a los campos, simplemente los dejé en blanco a propósito.
SELECT
@tmp_s := ept.`date_start`,
@tmp_e := IF(ept.`date_end` IS NULL, NOW(),ept.`date_end`),
@start := IF(DAYOFWEEK(@tmp_s)=1,@tmp_s + INTERVAL 1 DAY,(IF(DAYOFWEEK(@tmp_s)=7,@tmp_s + INTERVAL 2 DAY,@tmp_s)),
@end := IF(DAYOFWEEK(@tmp_e)=1,@tmp_e - INTERVAL 2 DAY,(IF(DAYOFWEEK(@tmp_e)=7,@tmp_e - INTERVAL 1 DAY,@tmp_e)),
@bizdays := CASE
WHEN DATEDIFF(@end,@start)>7 THEN CEIL((DATEDIFF(@end,@start)/7)*5)
WHEN DAYOFWEEK(@end)< DAYOFWEEK(@start) THEN DATEDIFF(@end,@start)-2
ELSE DATEDIFF(@end,@start)
END,
DATE(@start),
DATE(@end),
IF(@bizdays>=10,10,@bizdays)
FROM `employee_points` ept
WHERE ept.`date_start` > ''2011-01-01''
Si realmente quiere ignorar la existencia de los fines de semana, debe tratar algo que se origina en Sat / Sun como si se originó en lunes; y algo que termina el sábado / domingo como si realmente terminara el viernes. Por lo tanto, algo que comienza y termina en un fin de semana, debe ignorar tanto el inicio como el final. No creo que ninguna de las otras respuestas haga esto.
La siguiente función hace esto:
CREATE DEFINER=`root`@`localhost` FUNCTION `weekdayDiff`
(
edate datetime,
sdate datetime
)
RETURNS int
DETERMINISTIC
BEGIN
if edate>sdate
then
return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID(''+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1'', 2*(7 * WEEKDAY(sdate) + WEEKDAY(edate)) + 1, 2);
else
return -(5 * (DATEDIFF(sdate, edate) DIV 7) + MID(''+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1'', 2*(7 * WEEKDAY(edate) + WEEKDAY(sdate)) + 1, 2));
end if;
-- The following works unless both start and finish date are on weekends.
-- return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID(''0123444401233334012222340111123400001234000123440'', 7 * WEEKDAY(sdate) + WEEKDAY(edate) + 1, 1);
END;
En el lenguaje de la respuesta de Rodger, la tabla que creó la cadena de arriba está debajo (la única diferencia es -1 en vez de 0 para comenzar y terminar en un sábado / domingo):
| M T W T F S S
-|---------------------
M| +0 +1 +2 +3 +4 +4 +4
T| +4 +0 +1 +2 +3 +3 +3
W| +3 +4 +0 +1 +2 +2 +2
T| +2 +3 +4 +0 +1 +1 +1
F| +1 +2 +3 +4 +0 +0 +0
S| +0 +1 +2 +3 +4 -1 -1
S| +0 +1 +2 +3 +4 +4 -1
Solo para referencia futura. Ninguno de los anteriores funcionó para mí, pero una versión modificada de @Jeff Kooser:
SELECT (DATEDIFF(date_end, date_start)) -
((WEEK(date_end) - WEEK(date_start)) * 2) -
(case when weekday(date_end) = 6 then 1 else 0 end) -
(case when weekday(date_start) = 5 then 1 else 0 end) -
(SELECT COUNT(*) FROM holidays WHERE holiday>=date_start and holiday<=data_end)
Tenía este requisito y he escrito una función completa que puede calcular evitando horas de fin de semana y días festivos para un país determinado (usando una tabla separada). He puesto toda la función y los detalles en mi blog ( http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/ ) junto con la explicación y el diagrama de flujo y la creación de la tabla de vacaciones, etc. . Me encantaría ponerlo aquí, pero es demasiado tiempo ...
Ejemplo de problema resuelto:
Digamos que un incidente fue registrado el "viernes 10 de junio de 2016 a las 12:00" para un sitio en el "Reino Unido" que abre de 09:00 a 16:00. Este incidente se cerró el martes 14 de junio de 2016 a las 14:00.
Para el incidente anterior, la función debe calcular la edad como 960 minutos = 16 horas = [4 horas los viernes (12:00 a 16:00) + 7 horas los lunes (09:00 a 16:00) + 5 horas los martes ( 09:00 a 14:00)]
Tendrá que usar DATEDIFF para obtener el número de días entre dos fechas en MySQL. ES DECIR:
DATEDIFF(t.date_column_1, t.date_column_2)
Pero Stephane está en lo cierto, las vacaciones son federales y regionales. Necesita crear una tabla para almacenar las fechas y hacer referencia a ellas en su cálculo.
Una función que emula el NETWORKDAYS.INTL basado en la solución de Rodger Bagnall https://.com/a/6762805/218418
DELIMITER //
DROP FUNCTION IF EXISTS NETWORKDAYS//
CREATE FUNCTION NETWORKDAYS(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
RETURN (5 * (DATEDIFF(ed, sd) DIV 7) + MID(''0123444401233334012222340111123400001234000123440'', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1))+1;
END//
DELIMITER ;
Y para seleccionar
SELECT NETWORKDAYS(''2015-01-01 06:00:00'', ''2015-01-20 06:00:00'');
Yo uso esta solución, finalmente, por favor mira:
DROP FUNCTION IF EXISTS datediff_workdays;
CREATE FUNCTION datediff_workdays(start_date DATE, end_date DATE) RETURNS INTEGER
BEGIN
RETURN 5 * (DATEDIFF(end_date, start_date) DIV 7) + MID(''0123455501234445012333450122234501101234000123450'', 7 * WEEKDAY(start_date) + WEEKDAY(end_date) + 1, 1);
END
Below function will give you the Weekdays, Weekends, Date difference with proper results:
You can call the below function like,
select getWorkingday(''2014-04-01'',''2014-05-05'',''day_diffs'');
select getWorkingday(''2014-04-01'',''2014-05-05'',''work_days'');
select getWorkingday(''2014-04-01'',''2014-05-05'',''weekend_days'');
DROP FUNCTION IF EXISTS PREPROCESSOR.getWorkingday;
CREATE FUNCTION PREPROCESSOR.`getWorkingday`(d1 datetime,d2 datetime, retType varchar(20)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE dow1, dow2,daydiff,workdays, weekenddays, retdays,hourdiff INT;
declare newstrt_dt datetime;
SELECT dd.iDiff, dd.iDiff - dd.iWeekEndDays AS iWorkDays, dd.iWeekEndDays into daydiff, workdays, weekenddays
FROM (
SELECT
dd.iDiff,
((dd.iWeeks * 2) +
IF(dd.iSatDiff >= 0 AND dd.iSatDiff < dd.iDays, 1, 0) +
IF (dd.iSunDiff >= 0 AND dd.iSunDiff < dd.iDays, 1, 0)) AS iWeekEndDays
FROM (
SELECT dd.iDiff, FLOOR(dd.iDiff / 7) AS iWeeks, dd.iDiff % 7 iDays, 5 - dd.iStartDay AS iSatDiff, 6 - dd.iStartDay AS iSunDiff
FROM (
SELECT
1 + DATEDIFF(d2, d1) AS iDiff,
WEEKDAY(d1) AS iStartDay
) AS dd
) AS dd
) AS dd ;
if(retType = ''day_diffs'') then
set retdays = daydiff;
elseif(retType = ''work_days'') then
set retdays = workdays;
elseif(retType = ''weekend_days'') then
set retdays = weekenddays;
end if;
RETURN retdays;
END;
Thank You.
Vinod Cyriac.
Bangalore
DELIMITER //
DROP FUNCTION IF EXISTS NETWORKDAYS//
CREATE FUNCTION NETWORKDAYS(first_date DATE, second_date DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
SET diff = DATEDIFF(end_date, start_date);
RETURN (diff + 1)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = ''Sunday'' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = ''Saturday'' THEN 1 ELSE 0 END);
END//
DELIMITER ;
- prueba SELECT Networkdays (''2009-12-06'', ''2009-12-13'');
SELECT 5* (DATEDIFF(u.EndDate, u.StartDate) DIV 7) + MID(''1234555512344445123333451222234511112345001234550'', 7 * WEEKDAY(u.StartDate) + WEEKDAY(u.EndDate) + 1, 1)
Esto es cuando desea considerar los siguientes casos:
1) si startdate = enddate, duration = 1 y del mismo modo ...
Calculé la cadena usando la lógica mencionada en la respuesta más votada y obtuve los resultados que necesitaba.