Agrégation SQL sur plusieurs Date/Heure [Résolu]

[SQL_i]

Bonjour,

Je débute dans le SQL et je rencontre des problèmes avec une requête sur Oracle.

Voici les données du problème :

• un user peut intervenir sur une tâche ou plus
• une tâche peut être traitée par un user ou plus (A et B)
• la quantité est fixe pour une tâche (pas de somme à faire sur cette colonne lors de l'agrégat)

Le but étant d'avoir le suivi de chaque user par tâche.

Je souhaite récupérer en agrégeant pour chaque user et mission : l'user, la mission, la date et heure de début de l'intervention de l'user sur la mission et la date et heure de fin d'intervention.

Table :

AGENT ;MSSION ;debut ;fin  ; HEURE DEBUT;HEURE FIN; QT	
A;45;02/02/11;02/02/11; 8H40; 8H43;50
A;45;02/02/11;02/02/11; 9H25;10H48;50
A;45;03/02/11;03/02/11;11H25;12H48;50
B;45;02/02/11;02/02/11;13H25;13H48;50
B;45;03/02/11;03/02/11;15H25;16H48;50
B;45;04/02/11;04/02/11;16H25;16H48;50
C;44;02/02/11;02/02/11;16H58;17H50;80
C;44;02/02/11;02/02/11;20H30;21H30;80

Résultat attendu :

AGENT ;tache ;debut ;fin  ; HEURE DEBUT;HEURE FIN; qT
A;45;02/02/11;03/02/11; 8H40;12H48;50
B;45;02/02/11;04/02/11;13H25;16H48;50
C;44;02/02/11;02/02/11;16H58;21H30;80

Merci beaucoup de votre aide.

[punkoff]

Bonjour,

quelles requêtes avez-vous testé ?

Avez-vous une primary key sur cette table où est-elle tout simplement très mal modélisée ?

Bref dans tous les cas si vous concédez d'avoir en résultat des timestamp pour vos date de début / fin une solution assez simple pourra être trouvée avec

ceci :
http://sqlpro.developpez.com/cours/sqlaz/ensembles/

Et ceci :
http://www.dba-oracle.com/f_to_date.htm

[SQL_i]

Bonjour et merci de votre réponse.

Voici la requête que je teste :

Code :

1
2
3
  SELECT agent, tache, min(date_debut), min(heure_deb), max(date_fin), max(heure_fin), qt
    FROM TABLE
GROUP BY user, tache, qt

La table et le résultat que je dois obtenir est dans mon premier post.

Merci de votre aide.

[Waldar]

Attention : dans le select vous avez agent, dans le group by c'est user.
User est un mot réservé qui indique le compte utilisateur avec lequel vous êtes connecté à la base de données :

Code :

SELECT user FROM dual;

Sinon votre requête me paraît un bon début : ce qui pêche aussi ce sont les heures parce qu'elles sont stockées dans une colonne de format littéral type varchar2 !

Vos heures devraient être incluses dans vos dates de début et de fin.
Pouvez-vous modifier la structure de la table ou êtes-vous condamné à l'utiliser en l'état ?

[SQL_i]

Merci
Je rencontre un nouveau problème au niveau du calcul de la différence entre deux dates dans oracle.
En fait le :

date1
to_char(ah.import_date, 'DD/MM/YYYY HH24:MI:SS') ,
retourne 30/03/2011 10:51:13

date2
to_char(ol.load_date, 'DD/MM/YYYY HH24:MI:SS') ,
retourne 13/04/2011 16:49:50

Je n'arrive pas à faire la différence entre DATE1 et DATE2 pour obtenir le nombre d'heures dans oracle.
Avez vous une idée ?
Merci beaucoup

[Waldar]

La soustraction de deux dates renvoie un résultat en nombre de jour.
Il suffit donc de le multiplier par 24.
À vous de voir ensuite si vous désirez un arrondi ou un truncate de ce nombre :

Code :

SELECT (ol.load_date - ah.import_date) * 24 AS heures_diff

[SQL_i]

Envoyé par Waldar

La soustraction de deux dates renvoie un résultat en nombre de jour.
Il suffit donc de le multiplier par 24.
À vous de voir ensuite si vous désirez un arrondi ou un truncate de ce nombre :

Code :

SELECT (ol.load_date - ah.import_date) * 24 AS heures_diff

Merci beaucoup ..
Oracle me retourne un invalid number. Est-ce que c'est lié au format du champ date ?
Encore merci

[punkoff]

de quel type sont vos colonnes load_date et import_date ?

Si ce sont des char / varchar ne cherchez pas plus loin.

[SQL_i]

Envoyé par punkoff

de quel type sont vos colonnes load_date et import_date ?

Si ce sont des char / varchar ne cherchez pas plus loin.

Super ça fonctionne très bien et j'obtiens bien le nombre de jours de différence que je multiplie par 24..
La demande a évoluée depuis hier, en effet, je dois enlever également le nombre de jours NON OUVRE qui se trouve dans une table de référence :

DATE TIME ; OUVRE
DD/MM/YYYY ; 0 ce qui signifie non OUVRE et 1 pour OUVRE

Est-ce que je dois passer par un count distinct des non ouvrés et l'intégrer dans ma différence ?
Merci

[punkoff]

pourquoi un distinct ?

Mais une sous requête oui ca me semble le plus intéressant

[SQL_i]

C'est bon un exemple ci dessous
Cdt,

Code :

1
2
3
4
5
6
7
8
SELECT TP.NM, TP.DTDEB, TP.DTFIN,
         COALESCE(SUM(LEAST(CL.DAY + 1, TP.DTFIN) - GREATEST(CL.DAY, TP.DTDEB)), 0) AS JOURS_NON_OUVRES
    FROM TEMP TP
         LEFT OUTER JOIN CALENDAR CL
           ON CL.DAY BETWEEN TRUNC(TP.DTDEB) AND TRUNC(TP.DTFIN)
          AND (CL.DAY_NUMBER IN (6, 7) OR CL.FG_HOLIDAY = 1)
GROUP BY TP.NM, TP.DTDEB, TP.DTFIN
ORDER BY TP.NM ASC;