Regroupement par année avec champs de type CHAR contenant "Annee+Mois"

Hello,

je dois créer une requête et faire des stats sur des dates et avoir un résultat comme celui ci :

Periode | Nb Accident | Nb Heure
---------------------------------
2016 | 12 | 21520
2015 | 4 | 19510
2014 ........

le tout sur 5 ans glissant.

la requête contenant les données ( pour la colonne heure travaillé ) est :

Code:

---

1 2 3 4 5

SELECT HP_annee_mois [annee],SUM(hp_heure) [heure_travail_societe] FROM TPE_heure_effectif WHERE LEFT((TPE_heure_effectif.HP_annee_mois),4) >= YEAR(SYSDATETIME())-5 AND LEFT((TPE_heure_effectif.HP_annee_mois),4) <= YEAR(SYSDATETIME())+1 GROUP BY HP_annee_mois

---

le résultat donne :

année | heure_travail_societe
-----------------------------
201601 | 77121
201602 | 74000
201603 | 72120
201505 | 65512

mais j’aurai voulu plutôt un regroupement par année avec la somme des heures travaillés.

avez vous une idée ? une fonction a me conseillé ?

Merci :-)

HP_annee_mois est un champ de quel type ?

Si ça n'est pas du texte, je pense qu'il faudrait regarder du coté convert / cast pour convertir ce champ
en texte (s'il ne l'est pas)
Puis faire un substring
et enfin regrouper ça

donc bref en supposant que c'est du numérique, ce genre ... je n'ai pas se sqlserver sous la main ...

Code:

---

1 2 3 4

SELECT substring(CONVERT(varchar, HP_annee_mois),1,4) , etc ... ... GROUP BY 1

---

Citation:

---

Envoyé par vttman

Si ça n'est pas du texte, je pense qu'il faudrait regarder du coté convert / cast pour convertir ce champ
en texte (s'il ne l'est pas)

---

Vous êtes des ouf!
une date c'est une date donc selon la norme aujourd'hui un datetime2.
A priori pour "le mafioso" pour l'instant c'est du texte.
et oui donc un substring peut faire l'affaire mais c'est catastrophique de travailler comme ca!!!!!

si ta date est un datetime ca donnera un truc du style

Code:

---

1 2 3

Select year(madate), sum(hp_heure) From Matable GROUP BY YEAR(madate)

---

cordialement,

Citation:

---

Envoyé par Bernardos

Vous êtes des ouf!
...

---

;)

[EDIT] C'est vrai qu'à force d'en voir ... mais peut-on systématiquement tout remettre en cause ?
Il m'est arrivé, il y a un siècle, de travailler sur une base de données Oracle où les contraintes d'intégrité
avaient été supprimées car l'application client/serveur qui attaquait cette base de données
ne les supportait pas (les contraintes 8O) ... bon et alors on fait quoi eh bien on fait avec :calim2:

merci pour vos reponse.

en fait "HP_annee_mois" est un CHAR(6) ( la base de donnée est mal conçus ) et donc pour cette raison là que j'obtiens pas ce que je souhaite.

pour la requete :

Code:

---

1 2 3

Select year(madate), sum(hp_heure) From Matable GROUP BY YEAR(madate)

---

J'ai deja ça de preparer mais comme la requete contenu dans le left joint ( celle ou je cherche la solution ) contiendra les années et que le nombre heure.

le but c'est juste de regrouper le résultat par année pour qu'il passe de :

Code:

---

1 2 3 4 5 6

année | heure_travail_societe ----------------------------- 201601 | 77121 201602 | 74000 201703 | 72120 201505 | 65512

---

à

Code:

---

1 2 3 4 5 6

année | heure_travail_societe ----------------------------- 2016 | 140000 2017 | 72120 2015 | 65512

---

ensuite je n'aurai pas de soucis normalement pour le liée a mon autre table via LEFT JOIN et en prenant comme parametre la Date

Citation:

---

bon et alors on fait quoi eh bien on fait avec

---

et c'est comme ça qu'on en arrive à nager dans une piscine de merde...et qui plus est à trouver ça tout à fait normal!

Citation:

---

mais peut-on systématiquement tout remettre en cause ?

---

tout remettre systématiquement en cause non.
mais les grosses conneries oui!
si un client me dit qu'il veut que ses dates restent des char...je monte à la direction. et si ca bouge pas, je me casse et je les laisse dans la merde.
Je refuse de faire du mauvais travail!

Citation:

---

Il m'est arrivé, il y a un siècle, de travailler sur une base de données Oracle où les contraintes d'intégrité
avaient été supprimées car l'application client/serveur qui attaquait cette base de données
ne les supportait pas (les contraintes ) ...

---

il m'est arriver chez un client, de tomber aussi sur une base où toutes les clés avaient été désactivées.
Je suis monté à la direction qui a pris contact avec la direction du fournisseur. L'équipe de développeurs qui avait fait ça s'est fait virée.
une autre a réactivé toutes les contraintes et modifier les développement en conséquence.
Aujourd'hui, à ma connaissance, cette application tourne à merveille...

cordialement,

Code:

---

1 2 3

Select substring(HP_annee_mois,1,4), sum(heures) from tatable group by substring(HP_annee_mois,1,4)

---

ok ca marche merci !!!

la solution etait toute simple en fait ...

Citation:

---

Envoyé par Bernardos

et c'est comme ça qu'on en arrive à nager dans une piscine de merde...et qui plus est à trouver ça tout à fait normal!
...

---

Normal ? Non ! Habituel oui ...

Citation:

---

Envoyé par LeMafiozo

ok ca marche merci !!!

la solution etait toute simple en fait ...

---

La solution elle était donnée depuis le début ...

Citation:

---

La solution elle était donnée depuis le début ...

---

presque;) tu lui avais donné la piste mais tu n'avais fait tout le travail à sa place ;)
je pense que ce qui devais lui poser problème c'est le "sum"

Cordialement,

Citation:

---

Envoyé par Bernardos

tout remettre systématiquement en cause non.
mais les grosses conneries oui!
si un client me dit qu'il veut que ses dates restent des char...je monte à la direction. et si ca bouge pas, je me casse et je les laisse dans la merde.
Je refuse de faire du mauvais travail!

---

Et en INT, ça te va ?

Sinon arrête tout de suite de travailler sur MS Sql Server :

Code:

---

select top 500 active_start_date , active_end_date, active_start_time , active_end_time from [msdb].[dbo].[sysschedules]

---

:mouarf:

tiens au fait LeMafioso, vu que ton problème est résolu, n'oublie pas de mettre ton sujet en résolu ;)
Cordialement,

Citation:

---

Et en INT, ça te va ?

Sinon arrête tout de suite de travailler sur MS Sql Server :

---

Déjà je n'aime guerre ton ton (faut pas le dire à tantine ;))
ensuite on parlait de char et varchar et autre chaine de caractère, pas de int
nous savons tous les 2 (ou peut être pas) que

Citation:

---

le type DATETIME est représenté en interne par deux entiers de 4 octets. Le premier nombre représente le nombre
de jours depuis le 1er Janvier 1900 et le deuxième nombre équivaut au nombre de millisecondes écoulées depuis
zéro heure.(copyright Frédéric brouard :))

---

Donc ca ne me choque pas que sql server utilise des int dans ses tables systèmes...
Pour le datetime2, je sais qu'il est plus précis et qu'il respecte l'évolution de la norme mais je ne sais pas (encore) comment s'est représenté en interne.
du coup je profites de ce post pour poser la question à toi, SQL pro, Elsuket, aieuuuuuuuuuu et tous les autres
cordialement,

Bon ben en rentrant chez moi j'ai sauté sur la bible sqlserver de sql pro, elsuket ,david barbarin et christian sautou...

si j'ai bien compris le principe reste exactement le même sauf que maintenant le 2ème entier (celui pour le temps) possède 7 chiffres et la précision passe donc à 100 nano secondes.
j'ai répondu tout seul ;)
merci moi :) et surtout merci au livre et à ses auteurs :)
Cordialement

Citation:

---

Envoyé par Bernardos

Déjà je n'aime guerre ton ton (faut pas le dire à tantine ;))
ensuite on parlait de char et varchar et autre chaine de caractère, pas de int
nous savons tous les 2 (ou peut être pas) que
Donc ca ne me choque pas que sql server utilise des int dans ses tables systèmes...

---

Désolé pour le ton, j'essaie de faire court et direct et c'est semble parfois plus agressif que voulu.
Ah ben moi ça me choque mais pour autre chose que les histoires de bits.
As-tu essayer de manipuler des heures en int sans avoir les méthodes associés ?
00:01:01 devient 101 en int...
La conversion est pas évidente. J'ai une fonction qui convertit en datetime mais franchement, pourquoi ne pas y aller directement en datetime (ou les variantes) ?

tracasse c'était plus pour le jeu de mot ;)
Après, j'avais pas compris ta position, je pensais que tu étais partisan de cela.
Oui je ne connais pas la raison pour laquelle c'est fait ainsi, je présume que ca doit être historique...
si quelqu'un a des infos sur le sujet je suis preneur.
Cordialement,
Loïc

Citation:

---

Envoyé par LeMafiozo

Hello,

je dois créer une requête et faire des stats sur des dates et avoir un résultat comme celui ci :

Periode | Nb Accident | Nb Heure
---------------------------------
2016 | 12 | 21520
2015 | 4 | 19510
2014 ........

---

La solution la plus astucieuse et la plus performante serait de convertir vos pseudo date (en fait vos CHAR(6)) en vraies dates et de faire vos requêtes dessus. Cela peut être fait via une colonne calculée persistante, comme ceci :

Code:

---

1 2 3 4

ALTER TABLE TPE_heure_effectif ADD HP_annee_mois_DATE AS CONVERT(DATE, HP_annee_mois + '01', 112) PERSISTED;

---

Vous aurez alors de vraies dates au premier de chaque mois et les opérations en seront plus facile et plus performantes....

Votre requête devient :

Code:

---

1 2 3

SELECT YEAR(HP_annee_mois_DATE) AS [annee], SUM(hp_heure) [heure_travail_societe] FROM TPE_heure_effectif GROUP BY YEAR(HP_annee_mois_DATE)

---

Et pour vous former à SQL Server :
Pièce jointe 218909

A +

Citation:

---

Envoyé par Bernardos

le 2ème entier (celui pour le temps) possède 7 chiffres et la précision passe donc à 100 nano secondes.

---

Exact. Pour aller un peu plus loin, on peut s'en remettre à la documentation :

Citation:

---

6 octets pour des précisions inférieures à 3 chiffres ; 7 octets pour des précisions égales à 3 et 4 chiffres. Toutes les autres précisions requièrent 8 octets.

---

Donc pour stocker une valeur datetime, avec une précision à la milliseconde alors que datetime est à 3 millisecondes, on gagne 1 octet pour stocker la même valeur avec un datetime2. Sur une table de fait volumineuse, ça peut faire pas mal :)

@++ ;)

Tout d'abord tu ne sais même pas qui a créer la base de donnée d'accord... je fais avec qu'on me donne moi.

Je préfère PARFOIS avoir la réponse directement et ETUDIER LA RÉPONSE... comprendre COMMENT sa marche ????

Citation:

---

Envoyé par LeMafiozo

Tout d'abord tu ne sais même pas qui a créer la base de donnée d'accord... je fais avec qu'on me donne moi.

---

Vous n'avez visiblement pas compris ce que je vous ait dit. Je ne vous ais pas dit de changer la colonne, mais de rajouter une colonne calculée persistante sur laquelle vous allez baser vos requêtes. Cela fait partit des techniques traditionnelles de dénormalisation pour obtenir des performances, lorsque justement on a des bases mal modélisées !

A +