[Résolu] Méthode et Calcul d'une durée en fonction des horaires

[ipoza]

Bonjour la communauté,

Je sollicite votre aide car je débute en base de donnée et je n'ai pas encore les bons réflexes.
En PJ, le fichier odb de ma BDD simplifié pour le sujet.

Situation :

• Ma Table "REMPLACEMENT" enregistre des interventions avec comme champs / colonnes :
  id ; référence du Profil (intervenant) ; date ; heure de début ; heure de fin

• Une seconde table "PROFIL" enregistre les profils d'intervenant avec comme champs / colonnes :
  id ; tarif jour ; tarif nuit ; heure de bascule (horaire jour => horaire nuit)

• Une VUE SQL exporte les informations des 2 tables

• Un rapport génère la liste des Interventions par Remplaçant à partir de la VUE SQL précédemment créée

Tout va bien jusqu’ici

Mes deux problèmes sont les suivant :

• Je n'arrive pas à calculer les durées spécifiques aux horaires ouvrée en journées et aux horaires ouvrée la nuit
  je bloque au niveau de la syntaxe et de la logique
  
  Par exemple : Heure début = 14:00 Heure Fin = 21:00
  La durée d’heure ouvrée en journée va de 14:00 à 17:00 (déterminé par l’heure de bascule) soit 03:00 heures
  La durée d’heure ouvrée en soirée va de 17:00 (déterminé par l’heure de bascule) à 21:00 soit 04:00 heures

• Je n'arrive pas à déterminer quelle est la meilleur méthode pour y arriver (calcul + affichage), à savoir :
  - Ajouter aux enregistrement du tableau "REMPLACEMENT" les durées Jour et Nuit => méthode qui alourdirait la BDD mais allègerait la génération du rapport ?
  - Ajouter à la VUE les calculs des durées Jour et nuit
  - Ajouter au rapport des fonctions de calcul pour les durées jour et Nuit => méthode qui me parait la plus logique

Merci pour votre aide

[jeanmimi]

Bonjour,

Je n'arrive pas à calculer les durées spécifiques aux horaires ouvrée en journées et aux horaires ouvrée la nuit
je bloque au niveau de la syntaxe et de la logique

Il faut reconnaitre que les instructions SQL pour les calculs sur le temps qui passe ne sont pas des plus simples.
Pour m'en simplifier l'élaboration, la requête qui va calculer les temps d'interventions aux horaires de jour et aux horaires de nuit, en tenant compte que des interventions commencent en horaires de jour et se poursuivent en horaire de nuit, j'ai ajouté une Table supplémentaire avec un champ de bascule de valeur 17:00 h de façon à en récupérer la valeur dans les requêtes. Il y a certainement une alternative, mais je ne l'ai pas cherchée.
Tout ça pour dire que la requête Req_temps_tarifs_jour_nuit_montants semble faire le travail sans erreurs.
L'instruction SQL (à simplifier surement) est alors :

Code : Tout sélectionner

SELECT "REMPLACEMENT"."ref_Remplacant", "REMPLACEMENT"."ref_Profil", "REMPLACEMENT"."date", "REMPLACEMENT"."h_Debut", "REMPLACEMENT"."h_Fin", DATEDIFF( 'hh', "h_Debut", "h_Fin" ) AS "Temps_intervention", 
CASE WHEN ( "h_Debut" >= "h_Bascule_Nuit" ) THEN 0 WHEN ( "h_Bascule_Nuit" >= "h_Fin" ) THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) END AS "Temps_avant_bascule", "PROFIL"."tarif_Client_Jour", 
( CASE WHEN ( "h_Debut" >= "h_Bascule_Nuit" ) THEN 0 WHEN ( "h_Bascule_Nuit" >= "h_Fin" ) THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) END ) * "tarif_Client_Jour" AS "Facture_client_jour", 
DATEDIFF( 'hh', "h_Debut", "h_Fin" ) - CASE WHEN ( "h_Debut" >= "h_Bascule_Nuit" ) THEN 0 WHEN ( "h_Bascule_Nuit" >= "h_Fin" ) THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) END AS "Temps_apres_bascule", "PROFIL"."tarif_Client_Nuit", ( DATEDIFF( 'hh', "h_Debut", "h_Fin" )
 - CASE WHEN ( "h_Debut" >= "h_Bascule_Nuit" ) THEN 0 WHEN ( "h_Bascule_Nuit" >= "h_Fin" ) THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) END ) * "tarif_Client_Nuit" AS "Facture_client_nuit", "REMPLACEMENT"."id_Intervention", ( ( CASE WHEN ( "h_Debut" >= "h_Bascule_Nuit" ) THEN 0 WHEN ( "h_Bascule_Nuit" >= "h_Fin" ) THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) END ) ) * "tarif_Client_Jour" + ( DATEDIFF( 'hh', "h_Debut", "h_Fin" ) - CASE WHEN ( "h_Debut" >= "h_Bascule_Nuit" ) THEN 0 WHEN ( "h_Bascule_Nuit" >= "h_Fin" ) THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) END ) * "tarif_Client_Nuit" AS "Total_facture" FROM "REMPLACEMENT", "PROFIL" WHERE "REMPLACEMENT"."ref_Profil" = "PROFIL"."id_Profil"

[ipoza]

merci beaucoup ! Je vais regarder cela et vous redis

[ipoza]

Un grand Merci ! En effet çà fonctionne correctement et je n'aurais jamais trouvé la formule sans votre aide.

Merci aussi pour la modification du rapport.
Je ne voie pas comment ajouter, pour modification, un EN TÊTE de groupe (Date + Remplaçant) une fois le rapport créé.

[jeanmimi]

comment ajouter, pour modification, un EN TÊTE de groupe

Pour en savoir plus sur le Rapport, les ressources sont ici :
https://wiki.openoffice.org/wiki/FR/FAQ/Base/118

Dans l'exemple de la Base EN_v04 il y a bien en En-tête de Groupe les champs Date et Remplaçant. Les données de ces champs sont affichées sur chaque page et varient bien sûr en fonctions des données de la Table REMPLACEMENT

[ipoza]

J'ai trouvé une coquille dans la requête Req_temps_tarifs_jour_nuit_montants à la ligne permettant de calculer la colonne [Temps_avant_bascule], soit :

Code : Tout sélectionner

//calcul durée intervention AVANT bascule horaire
DATEDIFF( 'hh', "h_Debut", "h_Fin" ) 
- ( CASE WHEN ( DATEDIFF( 'hh', "h_Fin", "h_Bascule" ) ) < 0 
		THEN DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) 
    		ELSE 0 END ) 
AS "Temps_avant_bascule",

• Intervention 8h=>12h

21 - 8 = 4
17-12 = 5 => Positif
donc : 4 - 0 = 4 heures => OK

• Intervention 12h=>17h

17 - 12 = 5
17 - 17 = 0 => Positif
donc 5 - 0 = 5 heures => OK

• ntervention 16h=>20h

20 - 16 = +4
17 - 20 = -3 => Négatif => 20 - 17 = 3
donc 4 - 3 = 1 heure => OK

• Intervention 17h => 19h

19 - 17 = 2
17 - 19 = -2 => négatif => 19 - 17 = 2
donc 2 - 2 = 0 heure => OK

• Intervention 20h => 21h

21 - 20 = 1
17 - 21 = - 4 => négatif => 21 - 17 = 4
donc 1 - 4 = -3 heure => KO / pas possible
On devrait être à zero

Je cherche à rajouter à la section de la requête la chose suivante : forcer la valeur de Temps_avant_bascule à Zero
(sans succès)

Code : Tout sélectionner

CASE WHEN (h_Debut) > "h_Bascule_Nuit" THEN 0

ELSE DATEDIFF( 'hh', "h_Debut", "h_Fin" ) - ( CASE WHEN ( DATEDIFF( 'hh', "h_Fin", "h_Bascule" ) ) < 0 
		THEN DATEDIFF( 'hh', "h_Bascule_Nuit", "h_Fin" ) 
    		ELSE 0 END ) 
AS "Temps_avant_bascule",

[nounours2]

Salut Ipoza,

Pour la conversion heures/chiffre, je vous propose l'expression DATEDIFF().

En me basant sur vos deux tables, j'ai développé quelques requêtes en essayant de vous proposer une solution.

Salutations

nounours2

[jeanmimi]

Je cherche à rajouter à la section de la requête la chose suivante : forcer la valeur de Temps_avant_bascule à Zero
(sans succès)

En testant une période 20:00 à 21:00, ce code a fonctionné :

Code : Tout sélectionner

SELECT "REMPLACEMENT"."id_Intervention", "REMPLACEMENT"."date", "REMPLACEMENT"."h_Debut", "REMPLACEMENT"."h_Fin", 
CASE WHEN ( DATEDIFF( 'hh', "h_Debut", "h_Bascule_Nuit" ) ) < 0 THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) ELSE 0 END AS "Temps_intervention_si_debut_apres_bascule" 
FROM "REMPLACEMENT", "PROFIL" WHERE "REMPLACEMENT"."ref_Profil" = "PROFIL"."id_Profil"

Si en testant sur d'autres valeurs possibles (après 24:00 ça risque de poser problème), le résultat reste bon, il ne restera plus qu'à insérer cette instruction dans la Requête Req_temps_tarifs_jour_nuit_montants pour la compléter.

[nounours2]

@ jeanmimi

Mille mercis, Cher Mâitre.

nounours2

[ipoza]

Merci à vous.

Donc j'ai repris les syntaxe et la logique des requêtes pour en arriver là :

Code : Tout sélectionner

//Calcul Durée TOTAL intervention
DATEDIFF( 'hh', "h_Debut", "h_Fin" ) AS "Temps_intervention",

//Calcul Durée AVANT heure Bascule
CASE 
	//SI h_début commence APRES h_bascule ALORS Durée = 0
	WHEN ( DATEDIFF( 'hh', "h_Debut", "h_Bascule" ) ) < 0 THEN 0 	
	//SI h_fin se termine AVANT h_bascule ALORS Durée = Durée TOTAL intervention
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin"   ) ) < 0 THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) 
	//SINON SI h_Debut AVANT h_Bascule + h_Fin APRES h_Bascule ALORS Durée = Heure de bascule - heure de début
	ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule" )				
END AS "Temps_avant_bascule",

//Calcul Durée APRES heure Bascule
CASE 
	//SI h_fin se termine AVANT heure de bascule => Durée = 0
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) ) < 0 THEN 0 	
	//SINON Durée = Heure de fin - heure de bascule
	ELSE DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) 				
END AS "Temps_apres_bascule"

Cela à l'aire de fonctionner correctement pour tous les cas de figures

[micmac]

Bonjour,

Comment clore un sujet correctement s'il est résolu

[ipoza]

Je me permet de poser une dernière question avant de fermer ce poste svp

Pour chaque Colonne, la VUE SQL effectue les calculs sur l’ensemble des donnée issue des tables PROFIL et REMPLACEMENT pour donner les durées puis les montants de manière indépendante.

Est il possible de réutiliser les données calculé au sein même de la VUE SQL pour effectuer d'autre Calcul ?

par exemple :
J'ai actuellement ceci :

Code : Tout sélectionner

//Calcul Paie Heures Jour
CASE 
	WHEN ( DATEDIFF( 'hh', "h_Debut", "h_Bascule" ) ) < 0 THEN 0 	
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin"   ) ) < 0 THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) 
	ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule" )			
END * "tarif_Remp_Jour"
AS "Facture_client_jour",
 
//Calcul Paie Heures Soirée
CASE 
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) ) < 0 THEN 0 
	ELSE DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) 
END * "tarif_Remp_Nuit" AS "Facture_client_nuit",


//Calcul Paie Intervention (Heures Jour + Heures Soirée)
CASE 
	WHEN ( DATEDIFF( 'hh', "h_Debut", "h_Bascule" ) ) < 0 THEN 0 	
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin"   ) ) < 0 THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) 
	ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule" )			
END * "tarif_Remp_Jour"
+
CASE 
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) ) < 0 THEN 0 
	ELSE DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) 
END * "tarif_Remp_Nuit" AS "Total_facture" 

Pour le dernier calcul :
Peut on "Merger" un résultat : "Total_facture" = "Facture_client_jour" + "Facture_client_Nuit"
sans que la VUE ne reffase tous les calcul ?

[nounours2]

@ ipoza

Salut,

Etant donné que tu sais gérer une vue, tu peux aussi bien gérer une requête. Expressions SUM() et éventuellement GROUP BY().

Bien à toi

nounours2

[ipoza]

OK, je vais passer par la requête. Je clos ce sujet et vous remercie.

[Rafkus_pl]

Permettez-moi de proposer une amélioration pour CASE_WHEN, pourquoi calculer le nombre d'heures alors que vous ne pouvez que les comparer:

Code : Tout sélectionner

//Calcul Durée AVANT heure Bascule
CASE 
	WHEN ("h_Debut" >= "h_Bascule") THEN 0 	
	WHEN ("h_Bascule" >= "h_Fin" )  THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) 
	ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule" )			
END AS "Temps_avant_bascule",
 
//Calcul Durée APRES heure Bascule
CASE 
	WHEN ("h_Bascule" >= "h_Fin") THEN 0 
	WHEN ("h_Bascule" <= "h_Debut") THEN DATEDIFF('hh', "h_Debut", "h_Fin" )
	ELSE DATEDIFF('hh', "h_Bascule", "h_Fin" ) 
END  AS "Temps_apres_bascule",

Edit:
pour le cas où quelqu'un commence à travailler à, par exemple, 22h00 et se termine à, par exemple, 2h00, ajoutez simplement la condition suivante :
WHEN ("h_Debut" > "h_Fin") THEN DATEDIFF('hh', "h_Debut", "h_Fin" )+24
Cependant, cela doit être un peu différent.

[nounours2]

@ rakfus_pl

Excellente routine BASE. Remarquable.

Cependant, si vous considérez une simple journée ordinaire, vous conviendrez que la routine SQL est amplement suffisante....

Code : Tout sélectionner

SELECT "Salarié"."ID_salarié", "Salarié"."prénom", "Calcul heures"."date", "Calcul heures"."matinée", "Calcul heures"."aprèsmidi", ( "matinée" ) + ( "aprèsmidi" ) AS "ordinaire" FROM "Calcul heures", "Salarié"

Bien à vous

nounours2

[ipoza]

Bonjour,

Je poste une dernière MAJ de la requête SQL (la précédente avait encore une anomalie).
Il convient de différencier TROIS cas de calcul d’instincts pour les durées Avant et Après Heure de bascule (ou heure du soir)

Code : Tout sélectionner

//Calcul Durée TOTAL intervention
DATEDIFF( 'hh', "h_Debut", "h_Fin" ) AS "Temps_intervention",

//Calcul Durée AVANT heure Bascule
CASE 
	//SI h_début commence APRES h_bascule ALORS Durée = 0
	WHEN ( DATEDIFF( 'hh', "h_Debut", "h_Bascule" ) ) < 0 THEN 0 	
	//SI h_fin se termine AVANT h_bascule ALORS Durée = Durée TOTAL intervention
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin"   ) ) < 0 THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" ) 
	//SINON Durée = Heure de bascule - heure de début
	ELSE DATEDIFF( 'hh', "h_Debut", "h_Bascule" )				
END AS "Temps_avant_bascule",

//Calcul Durée APRES heure Bascule
CASE 
	//SI h_fin se termine AVANT h_bascule ALORS Durée = 0
	WHEN ( DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) ) < 0 THEN 0 
	//Si h_debut commence APRES h_bascule ALORS urée = Durée TOTAL intervention	
	WHEN ( DATEDIFF( 'hh', "h_Debut", "h_Bascule"   ) ) < 0 THEN DATEDIFF( 'hh', "h_Debut", "h_Fin" )
	//SINON Durée = Heure de fin - heure de bascule
	ELSE DATEDIFF( 'hh', "h_Bascule", "h_Fin" ) 				
END AS "Temps_apres_bascule",

[nounours2]

@ ipoza

Bonjour,

Tu voudras bien m'excuser, mais, en ce qui concerne la simulation de passage de date, celle-ci peut être simplement exécutée en 2 laps de temps, c.à.d. celui avant minuit et, pour la même personne, avec la date du lendemain, celui dès minuit.

Evidemment, dans la réalité des choses, le bureau du personnel aura choisi l'expression NOW() pour enregistrer lesdites heures, car elle tient compte aussi du changement de date.

Bien à toi

nounours2

[ipoza]

Il n'y a pas de simulation de passage de date.
Dans la logique de mon calcul / requête,
- si une intervention "déborde" après minuit (ce qui n'arrivera pas vis à vis du besoin) alors, il faut créer une nouvelle intervention
-la "bascule horaire de soirée" permet de différencier l'horaire de journée à l'horaire de soirée
- Il n'y a pas de "bascule horaire de matinée" permettant de différencier les horaire de journée et ceux du matin très tôt.
Dans ce cas, le calcul serait encore plus complexe : plus de cas de figure à anticiper

[nounours2]

@ ipoza

Chaise basculante ou bien, mieux encore tobbogan-piscine...

Le Grand Architecte du Temps s'est déclaré!

Et le petit comptable de Birkenau fera son arithmétique...

Slts

Nounours2

[jeanmimi]

- si une intervention "déborde" après minuit (ce qui n'arrivera pas vis à vis du besoin) alors, il faut créer une nouvelle intervention

Ou remplacer les champs au format Heure par des champs de Type Date/Heure [TIMESTAMP] qui gèreront le saut de date.

[nounours2]

@ ipoza

Jeanmimi a écrit:

Ou remplacer les champs au format Heure par des champs TIME_STAMP qui gèreront le saut de date.

Voici exemple extrait de mon fichier:

Code : Tout sélectionner

SELECT "date", "prénom", "entrée extra", "sortie extra", DATEDIFF( 'hour', "entrée extra", "sortie extra" ) AS "extras" FROM "Fiche horaire"

Slts

Nounours2

[Rafkus_pl]

Lorsque le travail est terminé après minuit, vous pouvez utiliser la formule suivante :

Code : Tout sélectionner

//Calcul Durée AVANT heure Bascule
CASE 
	WHEN ("h_Debut" >= "h_Bascule") THEN 0 
	WHEN ("h_Bascule" <= "h_Fin" ) OR ("h_Debut" > "h_Fin") THEN DATEDIFF( 'hh', "h_Debut", "h_Bascule" ) 
	ELSE DATEDIFF( 'hh', "h_Debut",  "h_Fin")			
END AS "Temps_avant_bascule",
 
//Calcul Durée APRES heure Bascule
CASE 
	WHEN ("h_Bascule" >= "h_Fin") AND ("h_Debut" <= "h_Fin") THEN 0 
	WHEN ("h_Bascule" <= "h_Debut") THEN DATEDIFF('hh', "h_Debut", "h_Fin" )
	ELSE DATEDIFF('hh', "h_Bascule", "h_Fin" ) 
END + CASEWHEN("h_Debut" > "h_Fin", 24, 0)  AS "Temps_apres_bascule"

J'ai supposé que personne ne travaillait 24h/24.

[nounours2]

Rueckfueß a écrit:

J'ai supposé que personne ne travaillait 24h/24.

Cool l'ami! à propos! T'as lu le bandeau rouge?

Slts

nounours2