Salut tout le monde,
Je chercher à faire une requête qui me donnerait la liste des jours entre deux dates.
J'ai (mal?) fais une recherche mais je ne trouve pas de solution.
Merci d'avance pour votre aide
Salut tout le monde,
Je chercher à faire une requête qui me donnerait la liste des jours entre deux dates.
J'ai (mal?) fais une recherche mais je ne trouve pas de solution.
Merci d'avance pour votre aide
Il faut que tu modélises et implémentes un calendrier.
Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
« Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
À la maison comme au bureau, j'utilise la suite Linux Mageïa !
Tu n'es pas forcément obligé de construire un calendrier aussi complexe que celui de SQLPro mais si tu veux tous les jours entre deux dates, il te faut a minima une table des dates.
Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
« Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
À la maison comme au bureau, j'utilise la suite Linux Mageïa !
C'est une limitation de Mysql, ou c'est pour toutes les bases de données la même chose ?
Aucun SGBD ne contient la liste des dates. Ce sera pareil pour tous les SGBD.
Les SGBD savent seulement faire des calculs sur les types DATE et DATETIME, souvent avec des fonctions qui leur sont propres.
Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
« Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
À la maison comme au bureau, j'utilise la suite Linux Mageïa !
On peut quand même s'en tirer avec des CTE !
Pas avec MYSQL !
Extrait de cet article :
SQL pauvre (MySQL ne respecte même pas la norme de 1992 !) :
...
- pas d'expression de tables (CTE)Comon Table Expression.Envoyé par rvzip64
Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
« Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
À la maison comme au bureau, j'utilise la suite Linux Mageïa !
Désolé je déterre le sujet, mais j'ai cherché longtemps et je pense que cette solution peut servir!
Situation:
Vous disposez d'une table A contenant une date de début et une date de fin (Exemple: Sejour)
Vous souhaitez lister les dates entre la date de début et la date de fin de sejour?
C'est faisable en une seule requête en suivant la procédure suivante:
- 1- Créer une table "enumeration" avec 2 colonnes: idEnumeration, no_jour
- 2- Lister dans cette table numéro de jours de l'année (1 à 366)
- 3- La requête: nous allons utiliser les fonctions from_days,to_days et dayofyear de mysql.
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT DISTINCT s.idSejour, s.dEntree,s.dSortie,FROM_DAYS(TO_DAYS(s.dEntree)-DAYOFYEAR(s.dEntree)+e.no_jour) as date_presence FROM sejour s INNER JOIN enumeration e ON e.no_jour BETWEEN DAYOFYEAR(s.dEntree) AND DAYOFYEAR(s.dSortie) WHERE s.idSejour=123456 ORDER BY date_presence ASC
- Ainsi pour un sejour donné (ici le sejour n° 123456), le moteur fait la jointure par rapport au n° de jour dans l'année avec DAYOFYEAR()
Et donc on associe aux deux dates de début et de fin tous les numéros de jours compris entre l'une et l'autre grâce à :
Code : Sélectionner tout - Visualiser dans une fenêtre à part INNER JOIN enumeration e ON e.no_jour BETWEEN DAYOFYEAR(s.dEntree) AND DAYOFYEAR(s.dSortie)- Puis dans le select, il nous reste à faire le calcul:
TO_DAYS() transforme la date d'entrée en un numéro de jour correspondant à une date: par exemple le résultat de TO_DAYS('1915-01-12') vaut x=699450
Inversement FROM_DAYS(699450) vaut y='1915-01-12'
on retranche à x le nombre de jour depuis le début de l'année DAYOFYEAR('1915-01-12') vaut z=12- il nous reste à ajouté un par un tous les numéros de jours récupérés dans la jointure donc + e.no_jour
Et finalement on retransforme en date avec FROM_DAYS()- Donc pour l'exemple admettons que le séjour démarre le 1915-01-12 et termine le 1915-01-15
La jointure va retourner les numéros de jours associés au dates à savoir e.no_jour = 12,13,14 et 15
x=699450 z1=12 e.no_jour=12 : x-Z1+e.no_jour=x et donc FROM_DAYS(x)=1915-01-12
x=699450 z1=12 e.no_jour=13 : x-Z1+e.no_jour=x+1 et donc FROM_DAYS(x+1)=1915-01-13
x=699450 z1=12 e.no_jour=14 : x-Z1+e.no_jour=x+2 et donc FROM_DAYS(x+2)=1915-01-14
x=699450 z1=12 e.no_jour=15 : x-Z1+e.no_jour=x+3 et donc FROM_DAYS(x+3)=1915-01-15
L'avantage de cette solution est qu'elle ne nécessite pas de générer tous les dates existantes dans une table qu'il faudrait remettre sans cesse à jour, les numéros de jours dans l'année sont fixes et toujours vrais (sauf si l'on change de calendrier pas taper!)
De plus, les fonctions MySQL sont compatibles avec les années bissextiles, inutile de modéliser un calendrier qui existe déjà.
Le seul inconvénient est plutôt du côté de la durée d'éxécution notamment si au lieu de lister les dates pour un seul séjour, on veux les dates de tous les séjours compris entre deux autres dates.
Ceci étant ça reste raisonnable (me concernant, quelques secondes tout au plus pour 20000 séjour). Tout dépend donc de l'utilisation qu'on veut en faire.
Bon code à tous.
Intéressant mais, comme je n'ai pas testé, je pose la question : est-ce que ça fonctionne si la période est à cheval sur deux années civiles ?
Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
« Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
À la maison comme au bureau, j'utilise la suite Linux Mageïa !
Effectivement il y a un hic pour ce cas là, je me suis un peu précipité...
Toutefois il y a une astuce qui fonctionne.
D'abord, il est nécessaire de modifier un peu notre table de numéros de jours, en effet le type doit être VARCHAR et non plus INTEGER
il faut y mettre tous les numéros de jours de 001 à 366 et non plus de 1 à 366. Ou alors on peut aussi simplement ajouter une colonne no_jour_txt et adapter un peu notre requête. Mais dans tous les cas, dans la jointure, nous n'aurons plus qu'à concaténer avec l'année comme ceci:
C'est un peu sale mais ça doit marcher. Je teste tout ça et reviens vers vous!
Code : Sélectionner tout - Visualiser dans une fenêtre à part INNER JOIN enumeration e ON (CONCAT(YEAR(s.dEntree),e.no_jour) BETWEEN CONCAT(YEAR(s.dEntree),DAYOFYEAR(s.dEntree)) AND CONCAT(YEAR(s.dSortie),DAYOFYEAR(s.dSortie))) OR (CONCAT(YEAR(s.dSortie),e.no_jour) BETWEEN CONCAT(YEAR(s.dEntree),DAYOFYEAR(s.dEntree)) AND CONCAT(YEAR(s.dSortie),DAYOFYEAR(s.dSortie)))
La requête:
Il n'est pas utile de modifier la table des numéros de jours puisqu'on peut le faire dynamiquement comme c'est fait dans la requête ci-dessus!
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 SELECT DISTINCT idSejour,s.dEntree,s.dSortie, CASE WHEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',e.cle_externe),3)) BETWEEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',DAYOFYEAR(s.dEntree)),3)) AND CONCAT(YEAR(s.dSortie),right(CONCAT('000',DAYOFYEAR(s.dSortie)),3)) THEN FROM_DAYS(TO_DAYS(s.dEntree)-DAYOFYEAR(s.dEntree)+e.cle_externe) ELSE DATE_FORMAT(FROM_DAYS(TO_DAYS(s.dEntree)-DAYOFYEAR(s.dEntree)+e.cle_externe),CONCAT(YEAR(s.dSortie),'-%m-%d')) END as date_presence, e.cle_externe FROM sejour s INNER JOIN enumeration e ON (CONCAT(YEAR(s.dEntree),right(CONCAT('000',e.cle_externe),3)) BETWEEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',DAYOFYEAR(s.dEntree)),3)) AND CONCAT(YEAR(s.dSortie),right(CONCAT('000',DAYOFYEAR(s.dSortie)),3)) AND e.type='dayofyear') OR (CONCAT(YEAR(s.dSortie),right(CONCAT('000',e.cle_externe),3)) BETWEEN CONCAT(YEAR(s.dEntree),right(CONCAT('000',DAYOFYEAR(s.dEntree)),3)) AND CONCAT(YEAR(s.dSortie),right(CONCAT('000',DAYOFYEAR(s.dSortie)),3)) AND e.type='dayofyear') WHERE idSejour=14029996 ORDER BY idSejour,date_presence ASC
Je vous poste également un résultat sous forme d'image pour un séjour qui chevauche deux années!
A noter un doublon pour le 01/01/2015, celui ci se retire très facilement en enlevant l'affichage de la cle_externe dans la requête ;-)
Par ailleurs, sachez que j'utilise ma table enumeration pour de nombreuses chose d'ou le champ "type" pour lequel je demande égal à "dayofyear"
c'est une manière pour moi de filtrer ;-)
Bon code à vous.
PS: Ce code ne fonctionne pas uniquement pour les séjours chevauchant 2 années, un séjour non chevauchant fonctionnera aussi...
Toutefois, je n'ai aucun cas et n'en n'aurais jamais pour lesquels le séjour dure plus d'un an donc inutile de me demander si ça fonctionne, libre à vous de tester, d'améliorer
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager