PlaceOweb Dotclear

Ayant testé la requête Mysql jours ouvrés

-- Les jours ouvrés
SELECT d1, d2, DATEDIFF(d2, d1) - (WEEK(d2) - WEEK(d1)) * 2 AS BizDays
FROM dates
ORDER BY d1, d2;

J'ai du trouvé autre chose, car sa solution ne fonctionne seulement si les dates sont inclues dans la même année (à cause de la différence de semaine WEEK).

SET @d1 = CAST('2011-12-31' AS DATE);
SET @d2 = CAST('2012-06-04' AS DATE);
SELECT 
-- @d1, @d2,
CAST( @d1 AS DATE),
CAST( @d2 AS DATE),
WEEK(@d1), WEEK(@d2), 
/*
WEEK(@d1,0), WEEK(@d2,0), 
WEEK(@d1,1), WEEK(@d2,1), 
WEEK(@d1,5), WEEK(@d2,5), 
WEEK(@d1,7), WEEK(@d2,7), 
*/
DATEDIFF(@d2, @d1),
DATEDIFF(@d2, @d1) - (WEEK(@d2) - WEEK(@d1)) * 2 AS BizDays
;
 
-- +--------------------+--------------------+-----------+-----------+--------------------+---------+
-- | CAST( @d1 AS DATE) | CAST( @d2 AS DATE) | WEEK(@d1) | WEEK(@d2) | DATEDIFF(@d2, @d1) | BizDays |
-- +--------------------+--------------------+-----------+-----------+--------------------+---------+
-- | 2012-06-01         | 2012-06-04         |        22 |        23 |                  3 |       1 |
-- +--------------------+--------------------+-----------+-----------+--------------------+---------+
-- Résulat correct : BizDays = 1 : 3 - (23-22) * 2
 
SET @d1 = CAST('2011-12-31' AS DATE);
SET @d2 = CAST('2012-06-04' AS DATE);
 
-- +--------------------+--------------------+-----------+-----------+--------------------+---------+
-- | CAST( @d1 AS DATE) | CAST( @d2 AS DATE) | WEEK(@d1) | WEEK(@d2) | DATEDIFF(@d2, @d1) | BizDays |
-- +--------------------+--------------------+-----------+-----------+--------------------+---------+
-- | 2011-12-31         | 2012-06-04         |        52 |        23 |                156 |     214 |
-- +--------------------+--------------------+-----------+-----------+--------------------+---------+
-- Résulat incorrect : BizDays 214 au lieu d'une valeur moindre à 156 : 156 - (23-52) * 2

Donc en repartant sur ces références SQL : Count business days between two dates

DROP FUNCTION IF EXISTS BizDaysInclusive;
DELIMITER |                                     
CREATE FUNCTION BizDaysInclusive( d1 DATE, d2 DATE )                           
RETURNS INT                                                                         
DETERMINISTIC                                                                          
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;
END;
|
DELIMITER ; 
 
 
SELECT BizDaysInclusive('2012-06-01','2012-06-04');
-- +---------------------------------------------+
-- | BizDaysInclusive('2012-06-01','2012-06-04') |
-- +---------------------------------------------+
-- |                                           2 |
-- +---------------------------------------------+
-- Entre le vendredi 01/06/2012 et le lundi 04/06/2012 on a bien 2 jours inclus de travail
 
SELECT BizDaysInclusive('2011-12-31','2012-06-04');
-- +---------------------------------------------+
-- | BizDaysInclusive('2011-12-31','2012-06-04') |
-- +---------------------------------------------+
-- |                                         111 |
-- +---------------------------------------------+

Références

• Mysql jours ouvrés
  • Common MySQL Queries : Count business days between two dates
• Requête qui prenne en compte les jours ouvrés
  • Calendrier, timing et horaires en SQL...
• Calcul de jours ouvrés