Dans ce cas, dans la base de données mysql, j'ai inséré un nouveau congé dans la table "Leave":
SELECT leave.ID_LEAVE, leave.ID_WORKER, leave.BEGIN_DATE, leave.END_DATE, time_format(SUM((datediff(leave.END_DATE, leave.BEGIN_DATE) + 1) * (time(leave.END_DATE) - time(leave.BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME' FROM leave GROUP BY leave.ID_LEAVE
Quand je résume le temps de congé dans la requête mysql ci-dessous:
+--------+---------+---------+-------------+----------+-------------------------- |ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE | END_DATE | +--------+---------+---------+---------+-------------+--------------------+------ | 5 | 10 | MARIO | NEED |2019-03-22 07:00:00 |2019-03-25 15:00:00 | +--------+---------+---------+-------------+----------+--------------------------
le résultat j'ai TEMPS DE CONGÉ = 32:00:00
Mais je vois que cela compte aussi les week-ends (samedi et dimanche). Je n'ai aucune idée de ce que je devrais changer si je pouvais compter sans week-end. Dans ce cas, l'heure de congé doit être 16:00:00. Quelqu'un peut-il s'il vous plaît quel type de requête puis-je changer. Merci pour toute adivce. :)
3 Réponses :
C'est un défi de taille @ Prochu1991 mais je pense que je parviens à créer une requête pour vous.
MODIFIER: La requête ci-dessous a quelques problèmes dans certaines conditions. Par conséquent, je ne recommande pas de l'utiliser mais je le laisse ici au cas où vous pourriez faire quelque chose à ce sujet:
SELECT *,IF(valid_leave_days=0, TIMEDIFF(end_date,begin_date),
-- Assuming that normal working hours is '08:00:00'. If more, you just need to change here.
SEC_TO_TIME(TIME_TO_SEC('08:00:00')*Valid_leave_days)) AS 'Total_leave_time'
-- So I convert 8 hours to seconds multiply with valid_leave_days calculated and convert it back to time. I think you understand this part.
FROM
(SELECT *,
-- This part where the CASE start is actually just determining how many leave days per person.
-- Then minus with the total of weekend per week (sat & sun = 2 days).
CASE
WHEN datedif<6 THEN datedif --if leave days are less than 6 days, it return datedif.
WHEN datedif=6 THEN datedif-1 --if leave days=6, datedif-1 day > because in any day you start you will surely get one weekend.
WHEN datedif BETWEEN 7 AND 12 THEN datedif-2 --if leave days between 7 and 12, datedif-2.
WHEN datedif=13 THEN datedif-3 -- from here you should get the idea.
WHEN datedif BETWEEN 14 AND 19 THEN datedif-4
WHEN datedif=20 THEN datedif-5
WHEN datedif BETWEEN 21 AND 26 THEN datedif-6
WHEN datedif=27 THEN datedif-7
WHEN datedif BETWEEN 28 AND 34 THEN datedif-8
-- Note that this is only up to 34 days. if you want to add more days, just make sure the calculation is correct.
END AS 'Valid_leave_days'
FROM
(SELECT *,DATEDIFF(end_date,begin_date) AS 'datedif' FROM LEAVE) a) b;
J'espère que vous comprendrez mon explication. Je continuerai de travailler avec cette requête et je verrai s'il existe un moyen de réduire certains processus (moins de requêtes).
MODIFIER 2: Ok, j'ai fait ça @ Prochu1991:
-- Query 6: Final calculation add SUM of total leave time GROUP BY ID_LEAVE,ID_WORKER.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
SEC_TO_TIME(SUM(TIME_TO_SEC(leave_TIME))) AS 'LEAVE TIME'
FROM (
Query 5: Calculating leave time on each date only if VALID_LEAVE_DATES=1.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
IF(VALID_LEAVE_DATES=1,SEC_TO_TIME(TIME_TO_SEC(TIME(end_date))-TIME_TO_SEC(TIME(begin_date))),0) AS 'LEAVE_TIME'
FROM (
-- Query 4: Add checking' if any of the dates are in the weekend, it will be set as 0.
SELECT leave_dates,
IF(DAYNAME(LEAVE_DATES) IN ('Saturday','Sunday'),0,1) AS 'VALID_LEAVE_DATES',
ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE FROM (
-- Query 3: In this part, the main reason is to create dates between BEGIN_DATE and END_DATE.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
-- concatenating extracted year-month with days generated from Query 1.
CONCAT_WS('-',DATE_FORMAT(BEGIN_DATE, '%Y-%m'),LPAD(days,2,0)) AS 'LEAVE_DATES' FROM
-- Query 1: This part is creating day value directly from query. If you run this individually, you'll get a day value from 0 to 39.
(SELECT 1 AS 'id'
a+b AS 'days' FROM
(SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) dd
-- Query 1 end here.
) ee
LEFT JOIN
-- Query 2: This is your original query. I removed the SUM in select.
(SELECT 1 AS 'id',
leave.ID_LEAVE,
leave.ID_WORKER,
leave.BEGIN_DATE,
leave.END_DATE
FROM leave GROUP BY leave.ID_LEAVE) cd
-- Query 2 end here.
ON ee.id=cd.id
WHERE days BETWEEN DAY(BEGIN_DATE) AND DAY(END_DATE) -- `WHERE` condition only take date value between BEGIN_DATE and END_DATE from Query 2.
ORDER BY LEAVE_DATES) LCALC -- Query 3 end here.
) vvv GROUP BY ID_LEAVE,LEAVE_DATES -- Query 4 end here.
) tuv -- Query 5 end here.
GROUP BY ID_LEAVE,ID_WORKER; -- Query 6 end here.
J'ai une question car Leave_dates est concerné n'est pas créé à partir de la table?
Non, mais c'est une plage entre begin_date et end_date dans votre table de congé. Je travaillais moi-même sur certains rapports et j'ai constaté que j'avais une plage de dates, mais pas toutes les dates comprises entre la plage de dates; qui est nécessaire pour mon objectif de rapport. Tout en travaillant sur votre condition, je trouve difficile d'utiliser la plage de dates car je risque de ne pas obtenir le résultat correct. Par conséquent, j'ai pensé que si j'avais le temps total de chaque date avec la condition d'exclure le week-end et de les résumer, j'obtiendrais le résultat.
ok, j'ai testé ce code et fonctionne extrêmement correctement. Merci beaucoup mon pote! :)
Pour le moment @ Prochu1991. Je travaille toujours sur la façon de faire une requête plus courte et, espérons-le, plus efficace. Je vous informerai si j'arrive à en faire un. Et au lieu d'utiliser DATEDIFF sur vos autres requêtes, vous pouvez essayer d'utiliser TIMEDIFF . Voyez si cela vous aidera.
Dans la base de données mysql fonctionne encore bien, mais quand j'ajoute ce code à .net le calcule avec les week-ends (ce qu'il ne devrait pas). Peut-être que je trouverai une solution de ce genre.
C'est vrai @ Prochu1991. Mon domaine de connaissances se limite uniquement à l'utilisation des requêtes MySQL car mon travail consiste généralement à récupérer des données directement à partir de la base de données. Le problème est donc que je ne sais pas comment implémenter une requête dans un programme car je ne sais pas quelle fonction fonctionne et non. Comme vous l'avez dit, dans un environnement MySQL, cette requête peut renvoyer le résultat souhaité. Désolé mais je suis limité à cela seulement. J'espère que vous trouverez un moyen de résoudre ce problème. Je vais voter pour vous et voir si cette question peut attirer plus d'attention.
J'ai une autre question qu'est-ce que cela signifie LCALC
ce n'est rien @ Prochu1991. C'est juste un alias pour la sous-requête. Vous pouvez le changer en tout ce que vous comprendrez. Je faisais cette abréviation de "LEAVE CALCULATION"> d'où il est devenu LCALC.
ahh ok @ tcadidot0 en ce qui concerne votre code, j'ai traité avec le code net. ;) Maintenant, je me demande avec le code que j'envoie comme réponse.
Salut @ tcadidot0 J'ai testé ce cas: le travailleur a congé 23/03/2019 - 01/04/2019 . Au lieu de calculer combien de temps il faut alors ... la requête est partie - ne montre rien. Avez-vous mis à jour votre requête?
Non, je n'ai rien modifié. C'est probablement à cause de la plage de dates. La requête ci-dessus fonctionne parce que votre plage de dates précédente se situe dans le même mois, tandis que 2019-03-23 - 2019-04-01 est entre deux mois différents. Je dois dire que j'ai vu cela venir. Il est peut-être temps d'essayer TIMEDIFF ?
Je suis passé à TIMEDIFF à partir de la ligne SEC_TO_TIME (TIME_TO_SEC (TIME (end_date)) - TIME_TO_SEC (TIME (begin in_date))) . Mais n'a pas tellement aidé.
Hey @ tcadidot0 quant à votre deuxième code édité, il compte aussi pour le mois prochain, mais il calcule toujours les week-ends aussi. bacause quand 2019-03-22 07:00:00 2019-03-31 15:00:00 il a 56:00:00 il devrait être 48:00: 00. Dans le cas où 2019-03-22 07:00:00 2019-03-31 15: 00: 00` au lieu de 64:00:00 devrait être trop 48:00:00
Mais je ne sais pas pourquoi: au cas où cela compte, au cas où 2019-03-20 07:00:00 - 2019-03-21 15:00:00 cela compte 08:00:00 à la place 16:00:00
@ Prochu1991 .. J'y pense depuis que j'ai mis à jour le code .. Ça ne marche pas .. soupir .. c'est plus compliqué que je ne le pensais
Désolé, j'ai publié une autre réponse. Pouvez-vous essayer ça? C'est une modification de la deuxième requête ci-dessus avec une vérification de begin_date:
+------------+-------------+-----------------------+-----------------------+-----------+-----------------------+---------------------+---------------------------------+--------------------+ | "ID_LEAVE" | "ID_WORKER" | "BEGIN_DATE" | "END_DATE" | "datedif" | "DAYNAME(begin_date)" | "valid_leave_days2" | "TIMEDIFF(end_date,begin_date)" | "Total_leave_time" | +------------+-------------+-----------------------+-----------------------+-----------+-----------------------+---------------------+---------------------------------+--------------------+ | "3" | "26" | "2019-03-20 07:00:00" | "2019-04-01 15:00:00" | "13" | "Wednesday" | "9" | "296:00:00" | "72:00:00" | | "4" | "22" | "2019-03-20 07:00:00" | "2019-03-20 15:00:00" | "1" | "Wednesday" | "1" | "08:00:00" | "08:00:00" | | "5" | "27" | "2019-03-01 07:00:00" | "2019-03-31 15:00:00" | "31" | "Friday" | "21" | "728:00:00" | "168:00:00" | | "6" | "28" | "2019-03-22 07:00:00" | "2019-03-31 15:00:00" | "10" | "Friday" | "6" | "224:00:00" | "48:00:00" | | "7" | "29" | "2019-03-20 07:00:00" | "2019-03-21 15:00:00" | "2" | "Wednesday" | "2" | "32:00:00" | "16:00:00" | | "8" | "30" | "2019-03-20 07:00:00" | "2019-03-22 15:00:00" | "3" | "Wednesday" | "3" | "56:00:00" | "24:00:00" | | "9" | "31" | "2019-03-28 07:00:00" | "2019-04-01 15:00:00" | "5" | "Thursday" | "3" | "104:00:00" | "24:00:00" | +------------+-------------+-----------------------+-----------------------+-----------+-----------------------+---------------------+---------------------------------+--------------------+
Pour votre commentaire "Mais je ne sais pas pourquoi: au cas où cela compte dans le cas 2019-03-20 07: 00:00 - 2019-03-21 15:00:00 il compte 08:00:00 au lieu de 16:00:00 ", j'ai compris que DATEDIFF n'inclut pas le BEGIN_DATE ou END_DATE dans le calcul. Disons que dans votre cas, si vous faites DATEDIFF (END_DATE, BEGIN_DATE) , cela comptera plutôt comme ceci, END_DATE-BEGIN_DATE donc 21 / 03-20 / 03 il obtient 1 jour seulement! Oh mon Dieu, je viens de comprendre à ce sujet aussi. J'ai vérifié si MySQL avait une fonction telle que DATE_COUNT mais ce n'est pas le cas. Par conséquent, j'ai fait une légère modification sur la requête du bas où j'ajoute DATEDIFF (end_date, begin_date-INTERVAL 1 DAY) AS 'dateif' . Ainsi, le - INTERVAL 1 DAY permet de commencer à compter les jours à partir du BEGIN_DATE .
P / S: Vous pouvez également faire comme ceci DATEDIFF (end_date + INTERVAL 1 DAY, begin_date) AS 'Datéif' .
EDIT : Voici le résultat que j'obtiens avec mes données de test en exécutant la requête ci-dessus.
SELECT *,TIMEDIFF(end_date,begin_date),IF(valid_leave_days2=0, TIMEDIFF(end_date,begin_date),SEC_TO_TIME(TIME_TO_SEC('08:00:00')*Valid_leave_days2)) AS 'Total_leave_time' FROM
(SELECT *,DAYNAME(begin_date),
CASE
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=6 THEN datedif-1
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 6 AND datedif < 13 THEN datedif-2
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=13 THEN datedif-3
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 13 AND datedif < 20 THEN datedif-4
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=20 THEN datedif-5
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 20 AND datedif < 27 THEN datedif-6
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=27 THEN datedif-7
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 27 AND datedif < 34 THEN datedif-8
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=5 THEN datedif-1
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 5 AND datedif < 12 THEN datedif-2
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=12 THEN datedif-3
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 12 AND datedif < 19 THEN datedif-4
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=19 THEN datedif-5
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 19 AND datedif < 26 THEN datedif-6
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=26 THEN datedif-7
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 26 AND datedif < 33 THEN datedif-8
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=4 THEN datedif-1
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 4 AND datedif < 11 THEN datedif-2
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=11 THEN datedif-3
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 11 AND datedif < 18 THEN datedif-4
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=18 THEN datedif-5
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 18 AND datedif < 25 THEN datedif-6
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=25 THEN datedif-7
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 25 AND datedif < 32 THEN datedif-8
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=3 THEN datedif-1
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 3 AND datedif < 10 THEN datedif-2
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=10 THEN datedif-3
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 10 AND datedif < 17 THEN datedif-4
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=17 THEN datedif-5
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 17 AND datedif < 24 THEN datedif-6
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=24 THEN datedif-7
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 24 AND datedif < 31 THEN datedif-8
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=31 THEN datedif-9
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=3 THEN datedif-1
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 3 AND datedif < 9 THEN datedif-2
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=9 THEN datedif-3
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 9 AND datedif < 16 THEN datedif-4
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=16 THEN datedif-5
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 16 AND datedif < 23 THEN datedif-6
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=23 THEN datedif-7
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 23 AND datedif < 30 THEN datedif-8
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=30 THEN datedif-9
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 30 AND datedif < 37 THEN datedif-10
ELSE datedif END AS 'valid_leave_days2' FROM
(SELECT *,DATEDIFF(end_date,begin_date-INTERVAL 1 DAY) AS 'datedif' FROM LEAVE) a) b;
J'apprécie votre réponse et votre aide. :) Mais je viens de tester ce code et dans ce cas, quand je l'ai lancé pour le cas 28-03-2019 7:00:00 à 01-04-2019 15:00:00 en temps total doit être 24:00:00 et non 264: 00: 00 . Mais je vois que tu y es presque. Cela ne meurt que le dernier espoir. ;)
en ce qui concerne la ligne de code SELECT *, DATEDIFF (end_date, begin_date-INTERVAL 1 DAY) AS 'dateif' FROM LEAVE est-ce une requête originale (comme la première)?
Le SELECT *, DATEDIFF (end_date, begin_date-INTERVAL 1 DAY) AS 'dateif' FROM LEAVE J'ai ajouté -INTERVAL 1 DAY pour inclure le compte BEGIN_DATE < / code> comme 1 jour. Quant au 28-03-2019 7:00:00 au 01-04-2019 15:00:00 retournant 264: 00: 00, je ne suis pas sûr car j'ai testé ici avec le même plage de dates mais elle renvoie 24:00:00 . Veuillez consulter la réponse mise à jour.
J'ai mis à jour avec le résultat de la requête. Je n'ai rien changé à la requête. :)
Pouvez-vous donner s'il vous plaît la base de données mysql que vous avez testée?
Entre quelle version de mysql avez-vous? J'ai 10.1.37-MariaDB
Actuellement, j'ai MySQL 4.1, MySQL 5.5 et MariaDB 10.3. Et j'ai testé cela sur MySQL 4.1 et MariaDB 10.3
Avez-vous testé sur MariaDB 10.1.37?
Juste testé sur MariaDB 10.1.37 64bit et obtenir le même résultat. :)
J'ai peur de ne pas avoir XAMPP @ Prochu1991. Je vous suggère d'essayer la solution de Sebastian. J'ai testé cette requête et c'est bien mieux que ce que j'ai fait
Mais d'un autre côté tu as commencé une nouvelle carrière en SQL presque comme moi! :)
haha, c'est vrai @ Prochu1991, c'est un bon exercice d'apprentissage et je suis heureux que quelqu'un finisse par trouver une très bonne réponse.
Vous pouvez utiliser la solution suivante à l'aide d'un tableau de calendrier ( basé sur cette solution ):
SELECT ID_LEAVE, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(TIME(end_date), TIME(begin_date)))))
FROM (
SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value
FROM
(SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) calendar INNER JOIN `leave` ON calendar.date_value BETWEEN DATE(leave.BEGIN_DATE) AND DATE(leave.END_DATE)
WHERE NOT WEEKDAY(date_value) IN (5, 6)
GROUP BY ID_LEAVE
C'est ce que j'ai essayé de faire. Je finis seulement par créer une table calendrier mais je n'ai aucune idée de comment les rejoindre et obtenir le résultat correct. : '(
En tant que deuxième code (modifié), j'ai testé et modifié les valeurs "Leave" et quelque chose s'est mal passé: dbfiddle.uk/...
@ Prochu1991 - Il semble qu'il y ait des problèmes dans certains cas spécifiques. merci pour cette info. Je supprime à nouveau la requête mathématique. La solution de table de calendrier fonctionne. C'était un essai.