Regrouper par mois glissants ?

**youpitralala** · 02/04/2008, 12h50

Bonjour,

J'ai une table T_CLIENT avec kID comme identifiant et une dD_INSC en tant que date d'inscription (format aaaammjj).

Je voudrais faire une requête SQL qui me compte le nombre d'inscrits / mois sur 12 mois (avec y compris le mois en cours) :

Par exemple : on est le 2 avril 2008

Je voudrais (colonne 1 = date, colonne 2 = count) :
200705 - 12
200706 - 35
200707 - 37
200708 - 41
200709 - 3
200710 - 0
200711 - 124
200712 - 129
200801 - 89
200802 - 241
200803 - 412
200804 - 17

Merci de votre aide, je tourne en rond.

Vincent

**ced** · 02/04/2008, 13h20

Bonjour,

J'imagine que la date est stockée comme une chaîne de caractère ?
Ca peut donner ça :

Code :

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

1
2
3
4
SELECT SUBSTRING(dD_INSC, 1, 6) AS mois, COUNT(*) AS nb_inscr
FROM T_CLIENT
GROUP BY SUBSTRING(dD_INSC, 1, 6)
ORDER BY 1

ced

**youpitralala** · 02/04/2008, 14h34

Bonjour,

On se rapproche de mon besoin sauf dans le cas où le total renvoie 0

Ce qui me faudrait :

200705 - 12
200706 - 35
200707 - 37
200708 - 41
200709 - 3
200710 - 0 <-------- j'en ai besoin
200711 - 124
200712 - 129
200801 - 89
200802 - 241
200803 - 412
200804 - 17

Ce que renvoie la requête :
200705 - 12
200706 - 35
200707 - 37
200708 - 41
200709 - 3
200711 - 124
200712 - 129
200801 - 89
200802 - 241
200803 - 412
200804 - 17

Merci,

Vincent

**ced** · 02/04/2008, 15h31

Là, ca devient plus compliqué... Il faut une table pivot à 12 lignes (une par mois) pour pouvoir s'en sortir...

ced

**youpitralala** · 02/04/2008, 15h59

Donc, si je comprends bien, il faudrait d'abord que je créer dynamiquement une table de 12 enregistements à partir de PHP et que je fasse un LEFT JOIN (ou RIGHT, selon le contexte)...

Bon, là maintenant, je continue de coincer...

1/ J'ai créé une table t_pivot_mois contenant 12 lignes, de 042007 à 042008.

2/ j'essaye de LEFT OUTER JOIN / INNER JOIN et autres jointures, ça ne me sort pas ce que je veux.....

Merci encore pour l'aide que vous pourrez m'apporter !

Vincent

**ced** · 02/04/2008, 17h46

Euh, j'espère que tu as plutôt mis les mois au format 'AAAAMM' et non pas 'MMAAAA'...
Sinon, pas étonnant que ça ne donne pas ce que tu veux...

Montre-nous ta requête.

ced

**jeremya** · 02/04/2008, 18h13

Envoyé par ced

Là, ca devient plus compliqué... Il faut une table pivot à 12 lignes (une par mois) pour pouvoir s'en sortir...

Une table contenant les chiffres de 0 à 9 suffit.
Avec des jointures, tu peux alors générer n'importe quel nombre.

Ensuite, à ces nombres, tu peux associer n'importe quelle information. Dans le cas présent, ce sera le nombre d'inscrits pour le mois correspondant.

**youpitralala** · 02/04/2008, 18h21

Bonjour,

Effectivement, les dates sont bien stockées au format yyyymmdd ! Désolé pour l'erreur dans le dernier Post.

Voici la requête que j'ai construite et qui regroupe déjà par mois, sauf que les lignes vides n'apparaissent pas.

Code :

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

1
2
3
4
SELECT COUNT(kID), CONCAT(MID(dD_INSC, 1, 4), MID(dD_INSC, 5, 2)) AS LEMOIS
FROM T_CLIENT
WHERE NUM_REGION = 2
AND dD_INSC >= '200704%';

Pour info, la table de pivot s'appellet t_pivot_mois et contient 1 champ `LeMois` et 12 lignes, de 200704 à 200804.

Encore une fois, merci de votre aide,

Vincent

**ced** · 02/04/2008, 18h28

Voilà la requête :

Code :

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

1
2
3
4
5
SELECT t.LeMois, COUNT(c.kID)
FROM t_pivot_mois t
LEFT JOIN t_client c ON t.LeMois = c.dD_INSC
GROUP BY t.LeMois
ORDER BY t.LeMois

La table pivot peut aussi contenir n'importe quoi, tant qu'il y a 12 lignes, mais le plus rapide (pour éviter d'avoir à passer par tout un tas de fonctions sur les dates), c'est encore qu'elle soit déjà renseignée avec les bons mois...

ced

**youpitralala** · 02/04/2008, 18h43

Code :

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

1
2
3
4
5
SELECT t.LeMois, COUNT(c.kID)
FROM t_pivot_mois t
LEFT JOIN t_client c ON t.LeMois = c.dD_INSC
GROUP BY t.LeMois
ORDER BY t.LeMois

J'ai bien fait ça en rajoutant ma clause WHERE et je n'affiche aucun enregistrement...

Code :

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

1
2
3
4
5
6
7
SELECT t.LeMois, COUNT(c.kID)
FROM t_pivot_mois t
LEFT JOIN t_client c ON t.LeMois = CONCAT( MID( c.dD_INSC, 1, 4 ) , MID( c.dD_INSC, 5, 2 ) )
WHERE c.Region = 2
AND c.dD_INSC >= '200704%'
GROUP BY t.LeMois
ORDER BY t.LeMois

Même si j'enlève le AND c.dD_INSC (qui fait maintenant un peu redondant), ça n'affiche aucun éléments.

Vincent

**ced** · 02/04/2008, 19h38

Oups

...
Désolé, c'est de ma faute...

J'ai oublié que le format de ta date était 'AAAAMMJJ'.

Code :

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

1
2
3
4
5
SELECT t.LeMois, COUNT(c.kID)
FROM t_pivot_mois t
LEFT JOIN t_client c ON t.LeMois = SUBSTRING(c.dD_INSC, 1, 6)
GROUP BY t.LeMois
ORDER BY t.LeMois

Comme ça, c'est mieux ?

**youpitralala** · 03/04/2008, 13h14

Envoyé par ced

Oups

...
Désolé, c'est de ma faute...

J'ai oublié que le format de ta date était 'AAAAMMJJ'.

Code :

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

1
2
3
4
5
SELECT t.LeMois, COUNT(c.kID)
FROM t_pivot_mois t
LEFT JOIN t_client c ON t.LeMois = SUBSTRING(c.dD_INSC, 1, 6)
GROUP BY t.LeMois
ORDER BY t.LeMois

Comme ça, c'est mieux ?

Bonjour, Ca ne marche pas mieux :

Pour info, la requête suivante me sort bien des lignes (selon la région, ça met de 8 à 12 lignes)

Code :

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

1
2
3
4
5
6
SELECT COUNT( kID ) , SUBSTRING(dD_INSC, 1, 6 ) AS LeMois
FROM t_client
WHERE numregion =2
AND t_client >= '200704%'
GROUP BY LeMois
LIMIT 0 , 30

Je vois pas le problème...

Vincent

**ced** · 03/04/2008, 13h24

Envoyé par youpitralala

AND t_client >= '200704%'

Ca, ça ne peut pas marcher

. Une table, supérieur ou égale à une chaîne de caractères ??? En plus, le % comme jocker de caractère n'a de sens qu'avec le mot clef LIKE...

J'ai testé ma requête avec un jeu de données locales, à partir des infos dont je disposais, et ça marche...

Peux-tu donner le schéma exact de tes tables t_client et t_pivot_mois (avec le type de chaque donnée), ainsi qu'un jeu de test dans chacune ?
Parce que là, sans ça, je coince...

ced

**youpitralala** · 03/04/2008, 15h36

Bonjour,

On va faire les choses simples avec les noms de champs et des tables.

Table t_pivot_mois
LeMois varchar(8)

Contient :
200704
200705
200706
200707
200708
200709
200710
200711
200712
200801
200802
200803
200804

Soit 13 enregistrements insérés automatiquement en fonction de la date du jour.

Ensuite, la table 2, de gestion des RMA (retours matériels dans le cadre d'un SAV).

Table RMA_PU
RMA_ID (clé) : varchar (20)
DATE_ENTREE_STOCK : varchar 20
NUM_CUSTOMER : varchar (5)

La liste des RMA du client 4 qui sont supérieurs à 200704 sort :

PUG2084 20070412
PUG2085 20070412
PUG2128 20070420
PUG2191 20070515
PUG2222 20070601
PUG2342 20070702
PUG2341 20070702
PUG2340 20070702
PUG2385 20070710
PUG2386 20070710
PUG2387 20070710
PUG2467 20070801
PUG2468 20070801
PUG2469 20070801
PUG2517 20070809
PUG2516 20070809
PUG2572 20070907
PUG2573 20070907
PUG2587 20070919
PUG2588 20070919
PUG2634 20070926
PUG2699 20071012
PUG2751 20071026
PUG2750 20071026
PUG2793 20071115
PUG2794 20071115
PUG2795 20071115
PUG2931 20071213
PUG2955 20071221

Soit 29 lignes

J'ai besoind d'obtenir ce résultat là :

200704 3
200705 1
200706 6
200707 5
200708 5
200709 3
200710 3
200711 3
200712 2
200801 0
200802 0
200803 0
200804 0

Il faut bien tenir compte du fait que j'ai besoin de mettre une clause sur le numéro du client.

Le but étant de faire un tableau avec des barres cumulées à partir de jpgraph. Je dois comparer les entrées avec les sorties et pour ce faire, il faut
absolument que le nombre de colonne soit identiques. D'où (l'idée était excellement mais je n'arrive pas à concrétiser) la fameuse table pivot avec un nombre de lignes fixe.

Encore une fois, merci de l'aide apportée.

Vincent

**ced** · 03/04/2008, 16h02

A tous les coups, c'est la condition sur le NUM_CUSTOMER qui faisait échouer la requête, parce qu'il faut la mettre dans la jointure et non pas dans une clause WHERE...
Ca donne donc :

Code :

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

1
2
3
4
5
SELECT SUBSTRING(t.LeMois, 1, 6), COUNT(r.RMA_ID)
FROM t_pivot_mois t
LEFT JOIN RMA_PU r ON SUBSTRING(t.LeMois, 1, 6) = SUBSTRING(r.DATE_ENTREE_STOCK, 1, 6) AND r.NUM_CUSTOMER = '4'
GROUP BY SUBSTRING(t.LeMois, 1, 6)
ORDER BY SUBSTRING(t.LeMois, 1, 6)

ced