Sélection d'"objets" par année - première partie [Débutant(e)]

**Domi2** · 29/07/2013, 16h22

Bonjour,

Malgré de nombreuses recherches, je n'ai pas trouvé ou pas su faire le "lien" entre ce que je désire faire et ce que j'ai pu lire.

Je connais un (petit) peu le langage SQL, je ne sais même pas si ce que je recherche à faire est possible.

Base de données utilisée, MySQL. Pour faire mes essais, j'utilise Access, mais au final, je devrais plutôt exécuter ma requête avec MySQL Workbench ou iReport.

Exposé du problème :

J'ai une table`immeuble` à laquelle est liée un table `logement` (un immeuble a évidemment plusieurs logements), qu'il me faudra compter, mais ce sera pour la deuxième partie de ma question, donc une autre discussion éventuellement.

Le premier problème sur lequel je butte.

Structure de la table immeuble :

NUMERO_DOSSIER
LETTRE_DOSSIER
DATE_DEBUT
DATE_FIN

1000 A 01.01.2000 NULL
1001 A 01.07.2000 31.12.2002
1002 A 01.01.2001 NULL
1003 A 01.02.2002 30.11.2003
1004 A 01.06.2004 NULL

Je désire dans un premier temps obtenir ceci :

2000 1000 A
2000 1001 A
2001 1000 A
2001 1001 A
2001 1002 A
2002 1000 A
2002 1001 A
2002 1002 A
2002 1003 A
2003 1000 A
2003 1002 A
2003 1003 A
2004 1000 A
2004 1002 A
2004 1004 A

Une requête telle que :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Year([DATE_DEBUT]) AS Annuel, immeuble.NUMERO_DOSSIER, immeuble.LETTRE_DOSSIER
FROM immeuble
GROUP BY Year([DATE_DEBUT]), immeuble.NUMERO_DOSSIER, immeuble.LETTRE_DOSSIER, immeuble.DATE_DEBUT;

ne répond évidemment pas à l'objectif.

Elle n'affiche que les dossier ayant commencé une année déterminée, et surtout elle occulte totalement l'année 2003, aucun dossier n'ayant commencé cette année là.

Et je ne peux pas utiliser une table temporaire.

Si vous avez des solutions, des tutos, des pistes, je suis preneur.

Merci d'avance.

**Waldar** · 29/07/2013, 17h18

Hello Domi2,

Y a-t-il une table calendrier dans le modèle ou la possibilité d'en créer une ?

Edit : pourquoi le dossier 1004 se retrouverait-il dans les années 2002 et 2003 ?

**Domi2** · 29/07/2013, 18h39

Envoyé par Waldar

Edit : pourquoi le dossier 1004 se retrouverait-il dans les années 2002 et 2003 ?

Bonjour Waldar,

Pour le dossier 1004, c'est une erreur de copier-coller de ma part

J'ai corrigé mon premier message dans ce sens.

Mais je vois que tu as bien saisi le principe.

Pour ce qui est d'une table calendrier, non, et je n'ai pas la possibilité d'en créer une. J'ai uniquement des droits en lecture seule sur la base.

La seule chose de tangible et de pérenne, c'est la borne inférieure, soit 1984. Aucun dossier ne peut avoir une DATE_DEBUT avant cette année-là.

Merci de t'intéresser à mon problème.

**Waldar** · 30/07/2013, 11h15

Sans table calendrier le problème est moins simple, surtout que je ne connais pas les mécanismes pour générer des lignes à la volée avec MySQL.
On peut toutefois essayer ainsi, mais si une année manque dans la table immeuble il y aura un trou :

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
22
23
24
25
26
27
28
  select cal.annee, imm.NUMERO_DOSSIER, imm.LETTRE_DOSSIER
    from (select year(DATE_DEBUT) as annee
            from immeuble
           union
          select year(DATE_FIN)
            from immeuble) as cal
    join immeuble as imm
      on cal.annee >= YEAR(imm.DATE_DEBUT)
     and (cal.annee <= YEAR(imm.DATE_FIN) or imm.DATE_FIN is null)
order by 1,2;
 
annee       NUMERO_DOSSIER LETTRE_DOSSIER
----------- -------------- --------------
2000        1000           A
2000        1001           A
2001        1000           A
2001        1001           A
2001        1002           A
2002        1000           A
2002        1001           A
2002        1002           A
2002        1003           A
2003        1000           A
2003        1002           A
2003        1003           A
2004        1000           A
2004        1002           A
2004        1004           A

**Domi2** · 30/07/2013, 15h19

Merci Waldar,

La requête que tu proposes n'est acceptée ni dans phpMyAdmin, ni dans iReport et encore moins dans Access, sans beaucoup d'explications.

J'ai essayé de la décortiquer, mais je ne vois pas ce qui n'est pas accepté (encore une fois, mon niveau en SQL est très faible).

Ceci dit, je suis un âne, un calendrier j'en ai un, une table qui contient tous les premiers jours de chaque mois depuis 1983 et qui doit être mise à jour systématiquement.

Et je suis deux fois un âne, je n'ai pas encore le réflexe. Je ne peux pas créer moi-même une table calendrier, d'accord, mais si je peux la justifier, je peux la faire intégrer dans la base de données. Du moment que je la paie...

J'ai donc fait une première requête, qryCalendrier, pour créer mon calendrier, avec un champ que j'ai appelé ANNEE_REF.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Year([DATE]) AS ANNEE_REF
FROM ipc
GROUP BY Year([DATE]);

Ensuite, en décortiquant ta requête, j'ai créé celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT qryCalendrier.ANNEE_REF, Year(DATE_DEBUT) AS ANNEE_DEBUT, Year(DATE_SORTIE) AS ANNEE_FIN, immeuble.NUMERO_DOSSIER, immeuble.LETTRE_DOSSIER, immeuble.TYPE_STATUT_IMMEUBLE_ID_FK
FROM qryCalendrier, immeuble
WHERE (((Year([DATE_DEBUT]))<=ANNEE_REF) AND ((Year(DATE_SORTIE))>=ANNEE_REF Or (Year(DATE_SORTIE)) Is Null) AND ((immeuble.TYPE_STATUT_IMMEUBLE_ID_FK)>=3))
ORDER BY qryCalendrier.ANNEE_REF, immeuble.NUMERO_DOSSIER, immeuble.LETTRE_DOSSIER;

Je l'ai faite dans le QBE d'Access, ici j'ai supprimé les caractères "parasites" [].

A priori, elle renvoie bien le résultat souhaité d'après le bref sondage que j'ai fait.

Maintenant, ce que je ne sais pas faire, c'est intégrer correctement ma sous-requête, qryCalendrier.ANNEE_REF, pour standardiser le SQL.

Encore une précision, j'ai ajouté un critère supplémentaire, immeuble.TYPE_STATUT_IMMEUBLE_ID_FK>=3, les dossiers avec statut 1 et 2 ne devant pas être intégré dans le résultat final.

Peux-tu m'aider à intégrer correctement cette sous-requête et / ou simplifier ma requête si c'est possible ?

**Waldar** · 30/07/2013, 15h44

Ok, ce sont de bons éléments !
Pour la partie année, il faut utiliser DISTINCT plutôt que GROUP BY.
En apparence ils se ressemblent, mais ils ont bien deux vocations différentes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DISTINCT Year([DATE]) AS ANNEE_REF FROM ipc

Pour standardiser le SQL, ceci devrait aller :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT cal.ANNEE_REF
       , Year(imm.DATE_DEBUT)  AS ANNEE_DEBUT
       , Year(imm.DATE_SORTIE) AS ANNEE_FIN
       , imm.NUMERO_DOSSIER
       , imm.LETTRE_DOSSIER
       , imm.TYPE_STATUT_IMMEUBLE_ID_FK
    FROM (SELECT DISTINCT Year([DATE]) AS ANNEE_REF FROM ipc) AS cal
    JOIN immeuble as imm
      ON cal.ANNEE_REF >= Year(imm.DATE_DEBUT)
     AND (cal.ANNEE_REF <= Year(imm.DATE_SORTIE) OR Year(imm.DATE_SORTIE) IS NULL)
   WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3
ORDER BY cal.ANNEE_REF      ASC
       , imm.NUMERO_DOSSIER ASC
       , imm.LETTRE_DOSSIER ASC;

À noter l'utilisation d'alias court pour les tables et sous-requêtes, je trouve que la requête est plus lisible ainsi.
À noter aussi que la fonction YEAR est courante chez Microsoft mais je ne pense pas qu'elle fasse partie du standard (qui est la fonction EXTRACT(year FROM MaColonne)). Il faut vérifier si MySQL la supporte.

**Domi2** · 31/07/2013, 07h05

Bonjour,

Effectivement, la fonction YEAR n'est pas reconnue par MySQL.

La requête finale est donc la suivante :

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
 
SELECT cal.ANNEE_REF
    , EXTRACT(YEAR FROM imm.DATE_DEBUT)  AS ANNEE_DEBUT
    , EXTRACT(YEAR FROM imm.DATE_SORTIE) AS ANNEE_FIN
    , imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
    , imm.TYPE_STATUT_IMMEUBLE_ID_FK
FROM (SELECT DISTINCT  EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal
JOIN immeuble AS imm
ON cal.ANNEE_REF >= EXTRACT(YEAR FROM imm.DATE_DEBUT)
    AND (cal.ANNEE_REF <=  EXTRACT(YEAR FROM imm.DATE_SORTIE) OR  EXTRACT(YEAR FROM imm.DATE_SORTIE) IS NULL)
WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3
ORDER BY cal.ANNEE_REF ASC
    , imm.NUMERO_DOSSIER ASC
    , imm.LETTRE_DOSSIER ASC;

Il me reste maintenant à ajouter le nombre de logements par immeubles, puis à faire la somme de ceux-ci par année, ce n'est pas gagné

La question spécifique de cette discussion est résolue.

Merci beaucoup, Waldar

**Waldar** · 31/07/2013, 10h26

Juste pour pinailler, on peut se passer de la conversion lorsqu'on teste la nullité de la date de fin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT cal.ANNEE_REF
       , EXTRACT(YEAR FROM imm.DATE_DEBUT)  AS ANNEE_DEBUT
       , EXTRACT(YEAR FROM imm.DATE_SORTIE) AS ANNEE_FIN
       , imm.NUMERO_DOSSIER
       , imm.LETTRE_DOSSIER
       , imm.TYPE_STATUT_IMMEUBLE_ID_FK
    FROM (SELECT DISTINCT EXTRACT(YEAR FROM DATE) AS ANNEE_REF FROM ipc) AS cal
    JOIN immeuble AS imm
      ON cal.ANNEE_REF >= EXTRACT(YEAR FROM imm.DATE_DEBUT)
     AND (cal.ANNEE_REF <= EXTRACT(YEAR FROM imm.DATE_SORTIE) OR imm.DATE_SORTIE IS NULL)
   WHERE imm.TYPE_STATUT_IMMEUBLE_ID_FK >= 3
ORDER BY cal.ANNEE_REF ASC
       , imm.NUMERO_DOSSIER ASC
       , imm.LETTRE_DOSSIER ASC;

Mais ça ne devrait strictement rien changer aux performances !
La relecture est peut-être un poil plus logique.

Sélection d'"objets" par année - première partie [Débutant(e)]

Langage SQL

Partager

Partager