Discussion :

[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 ........

le tout sur 5 ans glissant.

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

Code :

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

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 :-)

[vttman]

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 :

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

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

[Bernardos]

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 :

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

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

cordialement,

[vttman]

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 ) ... bon et alors on fait quoi eh bien on fait avec

[LeMafiozo]

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 :

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

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 :

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

1
2
3
4
5
6
année | heure_travail_societe
-----------------------------
201601 | 77121
201602 | 74000
201703 | 72120
201505 | 65512

à

Code :

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

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

[Bernardos]

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!

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!

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,

[Bernardos]

Code :

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

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

[LeMafiozo]

ok ca marche merci !!!

la solution etait toute simple en fait ...

[vttman]

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 ...

ok ca marche merci !!!

la solution etait toute simple en fait ...

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

[Bernardos]

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,

[Invité]

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 :

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

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

[Bernardos]

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

[Bernardos]

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

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,

[Bernardos]

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

[Invité]

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) ?

[Bernardos]

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

[SQLpro]

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 :

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

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 :

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

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 :


A +

[elsuket]

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 :

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

@++

[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.

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

[SQLpro]

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 +