Calcul une différence de date en fonction de jours fériés/travaillés et de plage horaire

Télécharger le projet
Commenter

Description

Cette fonction permet de calculer une différence en minutes entre deux dates/heures, dans le cadre (le plus souvent) d'une entreprise.
La fonction donne la différence et déduit :
- les nuits en dehors de la plage horaire
- les samedis et dimanches
- les jours fériés

Pour l'utiliser :
dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE(Date1, Date2) => Fait la différence Date2 - Date1 avec le traitement cité ci-dessus

Exemple :
SELECT dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE('14/05/2009 12:00:00', '14/05/2009 13:05:00') AS 'Différence (en minutes)'
Ou :
PRINT dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE ('07/05/2009 17:30:00', '11/05/2009 08:05:00')

Pour le calcul dynamique des jours fériés, je me suis basé sur ce script :
http://www.sqlfr.com/codes/ORACLE-SQL-SERVER-2K-FONCTION-RETOURNANT-STATUS-OUVRE_27798.aspx

Source / Exemple : 


--====================================================================--
--    Ensemble de fonctions de calculs de date avec jours fériés
--====================================================================--

-----------------------------------------------------------------------
-- FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE
-- calcule la différence en minutes entre deux dates
-- en fonction des horaires de l'ENTREPRISE : 
-- ici : 08h00 - 18h00
--
-- Déduit les nuits entre 18h00 et 08h00 le lendemain
-- ainsi que les week-ends et les jours fériés
-----------------------------------------------------------------------
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE')
	DROP FUNCTION dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE
GO
CREATE FUNCTION FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE (@Date1 DATETIME, @Date2 DATETIME)
	RETURNS INT AS
	BEGIN
		DECLARE @DateCreCalcul DATETIME
		DECLARE @NbJourneesNonTravaillees INT
		DECLARE @NbNuits INT

		SET @DateCreCalcul = @Date1
		-- Si Date1 inférieure à l'heure d'ouverture
		IF DATEPART(hour, @Date1) < 8
		BEGIN
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @Date1) + 8, @Date1)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END
		-- Si Date1 supérieure à l'heure de fermeture
		IF DATEPART(hour, @Date1) >= 18 BEGIN
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @Date1) + 18, @Date1)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END
		-- Si Date1 est un dimanche, on ajoute 1 jour et on commence à l'heure d'ouverture
		IF DatePart(weekday, @Date1) = 7 BEGIN
			SET @DateCreCalcul = DATEADD(day, 1, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @DateCreCalcul) + 8, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END
		-- Si Date1 est un samedi, on ajoute 2 jours et on commence à l'heure d'ouverture
		IF DatePart(weekday, @Date1) = 6 BEGIN
			SET @DateCreCalcul = DATEADD(day, 2, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @DateCreCalcul) + 8, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END

		SET @NbJourneesNonTravaillees = 0	
		SET @NbNuits = DATEDIFF(day, @DateCreCalcul, @Date2)
		-- Si les 2 dates sont à des jours différents, on cherche le nombre de jours NON travaillés entre les 2 dates
			DECLARE @Compteur_NbNuits int;
			SET @Compteur_NbNuits = 0;
			
			-- Dans une boucle, on balaye tous les jours entre les 2 dates pour compter le nombre de jours non travaillés
			WHILE @Compteur_NbNuits < @NbNuits
			BEGIN
				SET @Compteur_NbNuits = @Compteur_NbNuits + 1;
				IF dbo.FN_JourTravailleFerie(DATEADD(day, @NbJourneesNonTravaillees + 1, @DateCreCalcul), 1) <> 'X'
				BEGIN 
					SET @NbJourneesNonTravaillees = @NbJourneesNonTravaillees + 1
				END
			END
		
		-- On calcule la différence en minutes et on retire :
		--  - la durée des des nuits en minutes
		--  - la durée des journées non travaillées en minutes
		RETURN datediff(minute, @DateCreCalcul, @Date2) - 840*@NbNuits - 600*@NbJourneesNonTravaillees
END
GO

-----------------------------------------------------------------------
-- Ensembles de fonctions qui déterminent si un jour est travaillé,
-- week-end ou férié
--
-- Entrée : date à tester,
--			Considérer le samedi comme un jour férié ?
--				Par défaut les samedi sont considérés comme fériés
--				0 : est considéré travaillé
--				1 : est considéré férié
--
-- Sortie :	chaine de caractère
--				X :	 jour travaillé
--				WE : Week-end
--				JF : Jour férié
--
-- Exemples d'appel de la fonction :
--			dbo.FN_JourTravailleFerie(getdate(), 1)
--			dbo.FN_JourTravailleFerie('21/04/2009', 1)
-----------------------------------------------------------------------

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Mini' AND type = 'FN') DROP FUNCTION Mini
GO
CREATE FUNCTION Mini(@a float, @b float) returns float
AS
BEGIN
	DECLARE @f float
	if @a < @b 
		SET @f = @a
	ELSE
		SET @f = @b
	return @f
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Maxi' AND type = 'FN') DROP FUNCTION Maxi
GO
CREATE FUNCTION Maxi(@a float, @b float) returns float
AS
BEGIN
	DECLARE @f float
	IF @a > @b 
		SET @f = @a
	ELSE
		SET @f = @b
	return @f
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'IsCorrectDate' AND type = 'FN') DROP FUNCTION IsCorrectDate
GO
CREATE FUNCTION IsCorrectDate (@JJ integer, @MM integer, @AAAA integer) returns integer 
AS
BEGIN
	DECLARE @FlagReturn integer
    -- Hors plage
    IF @JJ < 1 Or @JJ > 31 Or @MM < 1 Or @MM > 12 
	  BEGIN
        SET @FlagReturn = 0
      END
    ELSE
    BEGIN
    -- Mois de 30 jours
    IF @JJ = 31 AND (@MM = 4 Or @MM = 6 Or @MM = 9 Or @MM = 11) 
      BEGIN
        SET @FlagReturn = 0
      END
    ELSE
    BEGIN
    -- Mois de février
     IF @MM = 2 
       BEGIN
         IF @JJ <= 28 
           BEGIN
             SET @FlagReturn = 1
           END
         ELSE
           BEGIN
             IF @JJ > 29 
               BEGIN
                 SET @FlagReturn = 0
               END
             ELSE  
               BEGIN
             	 IF Not (@JJ = 29 AND ((@AAAA % 4 = 0 AND @AAAA % 100 <> 0) Or @AAAA % 400 = 0))
             	   BEGIN
                     SET @FlagReturn = 0
                   END
                 ELSE
                   BEGIN
                     SET @FlagReturn = 1
                   END
               END
           END          
       END
       ELSE -- mois autre que fevrier
         BEGIN
         	SET @FlagReturn = 1
         END	
    END
    END
    Return @FlagReturn
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'ConvertDate' AND type = 'FN') DROP FUNCTION ConvertDate
GO
CREATE FUNCTION ConvertDate (@dtDate DateTime) returns varchar(4) 
AS
BEGIN
  DECLARE
  	@iJour integer,
  	@iMois integer,
  	@sJour varchar(2),
  	@sMois varchar(2),
  	@sResult varchar(4)

  SET @iJour = datepart(dd, @dtDate)
  SET @iMois = datepart(mm, @dtDate)

  SET @sJour = CAST(@iJour AS VARCHAR)
  SET @sMois = CAST(@iMois AS VARCHAR)

  IF len(@sJour) = 1 
    BEGIN
      SET @sJour = '0' + @sJour
    END
    
  IF len(@sMois) = 1 
    BEGIN
      SET @sMois = '0' + @sMois
    END
  
  SET @sResult = @sJour + @sMois

  RETURN @sResult
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'FN_JourTravailleFerie' AND type = 'FN') DROP FUNCTION FN_JourTravailleFerie
GO
CREATE FUNCTION FN_JourTravailleFerie (@dtDate DateTime, @iSamediFerie integer = 1) returns varchar(2)
AS
BEGIN

-- Cette fonction indique si le jour passé en argument est férié.
-- Par défaut les samedi sont considérés comme fériés, mais le second
-- paramètre permet de changer cela.

DECLARE 
	@lgA bigint,
	@lgMPaq bigint,
	@lgJPaq bigint,
	@lgTmp1 bigint, 
	@lgTmp2 bigint, 
	@lgTmp3 bigint,
	@stDate varchar(10),
	@dtPaq DateTime,
	@stPaq varchar(10),
	@blFerie integer,
	@stType varchar(2)

 
SET @stType = 'X'
-- Dimanche (et samedi)

IF @iSamediFerie = 1 
  BEGIN
    IF DatePart(weekday, @dtDate) = 7 OR DatePart(weekday, @dtDate) = 6 
      BEGIN
        SET @blFerie = 1
      END
    ELSE
      BEGIN
        SET @blFerie = 0
      END
  END  
ELSE
  BEGIN
    IF DatePart(weekday, @dtDate) = 7 
      BEGIN
        SET @blFerie = 1
      END
    ELSE
      BEGIN
        SET @blFerie =0
      END
  END

IF @blFerie = 1 
  BEGIN
    SET @stType = 'WE'
  END
  
IF @blFerie = 0 
  BEGIN
    SET @stDate = dbo.ConvertDate(@dtDate)
    -- Jours fériés fixes (1er janvier, 1er mai, 8 mai, 14 juillet, ...)
    IF @stDate IN ('0101','0105','0805','1407','1508','0111','1111','2512')
      BEGIN
        SET @blFerie = 1
      END
  END
 
IF @blFerie = 0
  BEGIN
    SET @lgA = Year(@dtDate)
    SET @lgTmp1 = (19 * (@lgA % 19) + 24) % 30
    SET @lgTmp2 = dbo.Mini(@lgTmp1, dbo.Maxi(28, @lgTmp1) - 1)
    SET @lgTmp3 = 28 + @lgTmp2 - ((3 + @lgA - 1900 + ((@lgA - 1900) / 4) + @lgTmp2) % 7)
    SET @lgMPaq = 3 + (@lgTmp3 / 32)
	-- Remarque Le calcul du jour de pâques ne fonctionne pas toujours. L'arrondi est mauvais lorsqu'il y a une division : mettre un TRUNC(ladivision,0) pour garder la partie entière.
	-- http://www.sqlfr.com/codes/ORACLE-SQL-SERVER-2K-FONCTION-RETOURNANT-STATUS-OUVRE_27798.aspx
	-- SET @lgMPaq = 3 + round((@lgTmp3 / 32), 0)
    
    IF (@lgTmp3 < 32) 
    	BEGIN
    	  SET @lgJPaq = @lgTmp3 
    	END
    ELSE 
    	BEGIN
    	  SET @lgJPaq = @lgTmp3 - 31
    	END
    	
    -- Construction de la date du dimanche de Pâques
    SET @stPaq = CAST(@lgJPaq AS varchar) + '/' + CAST(@lgMPaq AS varchar) + '/' + CAST(@lgA AS varchar)
    SET @dtPaq = CAST (@stPaq AS DateTime)
    -- Jours fériés mobiles (lundi de pâques, ascension, lundi de pentecôte)
    -- Pâques et Pentecôte non testés, puisqu'ils tombent tous les deux un dimanche.
    
    IF (@dtDate = (@dtPaq + 1)) Or (@dtDate = (@dtPaq + 39)) Or (@dtDate = (@dtPaq + 50))
      BEGIN
        SET @blFerie = 1
      END
    
  END
 
IF @blFerie = 1 AND @stType = 'X'
  BEGIN
    SET @stType = 'JF'
  END 
 
 RETURN @stType  
 
END 
GO

Conclusion :


L'heure pour Date1 est retravaillée pour coller à la plage horaire :
- si elle est inférieure à l'heure d'ouverture, on prend en compte l'heure d'ouverture
- si elle est supérieure à l'heure de fermeture, on prend en compte l'heure de fermeture
L'heure pour Date2 n'est retravaillée.

Remarque : j'ai développé cette fonction pour mon cas précis. Elle devra probablement être adaptée pour d'autres cas (horaires d'ouverture/fermeture par exemple) mais cela donne un départ, c'est pour ça que je la partage.
Vos commentaires sont les bienvenus.
Télécharger le projet

Codes Sources

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.