[Oracle] calcul de date de sortie et de la durée de séjour

**nizniz** · 23/03/2006, 13h43

bonjour,
voici quelques précisions concernant ma requête:
j'ai une base de donnée ORACLE.
Dans un premier temps je veux crée une table (STATIQUE) à partir de plusieurs autres tables (PASS,LOCINFO,LOCAL,SALLE,ORIENT).
A ma table TAB je dois rajouté les champs DATE_SORTIE et DUREE_SEJOUR(DATE_SORTIE-DATE_ENTREE) par secteur.
En utilisant la requête LEFT JOIN:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
 
CREATE TABLE STATIQUE AS
SELECT TAB.NUM, TAB.DATE_ENTREE, TAB.SECTEUR, MIN(TABB.DATE_ENTREE) AS DATE_SORTIE  
FROM 
(SELECT 
   P.NUM,
   P.STATUT,
   I.DATE_ENTREE,
   L.SECTEUR,
   O.DESTINATION
FROM
PASS P, LOCINFO I, LOCAL C, SALLE L, ORIENT O
WHERE 
P.CODE = C.CODE_P AND
P.CODE = O.CODE_P AND
C.CODE = I.CODE_LOC AND 
L.CODE_SAL = I.SALL 
AND
O.MODIF_DATE = ( select max(O.MODIF_DATE) from ORIENT O where P.CODE = O.CODE_P )
) TAB
LEFT JOIN 
(SELECT 
   P.NUM,
   P.STATUT,
   I.DATE_ENTREE,
   L.SECTEUR,
   O.DESTINATION
FROM
PASS P, LOCINFO I, LOCAL C, SALLE L, ORIENT O
WHERE 
P.CODE = C.CODE_P AND
P.CODE = O.CODE_P AND
C.CODE = I.CODE_LOC AND 
L.CODE_SAL = I.SALL 
AND
O.MODIF_DATE = ( select max(O.MODIF_DATE) from ORIENT O where P.CODE = O.CODE_P )
) TABB
ON TAB.NUM = TABB.NUM
AND TAB.SECTEUR=TABB.SECTEUR
AND TABB.DATE_ENTREE > TAB.DATE_ENTREE
GROUP BY TAB.NUM, TAB.DATE_ENTREE, TAB.SECTEUR ;

le résultat est le suivant:
sachant que j'ai 4 secteut: sect1,sect2,sect3,sortie

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
 
NUM      DATE_ENTREE             SECTEUR     DATE_SORTIE
--------------------------------------------------------------------
1        13/02/05 14:15          sect1       13/02/05 16:25  
1        13/02/05 16:25          sect2       14/02/05 09:05  
1        14/02/05 09:05          sect2       14/02/05 16:50  
1        14/02/05 16:50          sortie        
 
2        13/02/05 17:15          sect1       13/02/05 19:25  
2        13/02/05 19:25          sect2       17/02/05 09:05  
2        17/02/05 09:05          sect3       17/02/05 16:50  
2        17/02/05 16:50          sortie        
 
3        10/01/05 17:15          sect1       13/01/05 19:25  
3        13/01/05 19:25          sect2       17/01/05 09:05  
3        17/01/05 09:05          sect2       17/01/05 16:50  
3        17/01/05 16:50          sect2       17/01/05 16:55  
3        17/01/05 16:55          sortie

je dois rajouté la durée de séjour par secteur et groupé les secteur pour chaque valeur du champ NUM
exemple : pour le NUM 3 obtenir:

Code :

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

1
2
3
4
 
3        10/01/05 17:15          sect1       13/01/05 19:25  
3        13/01/05 19:25          sect2       17/01/05 16:55  
3        17/01/05 16:55          sortie

merci d'avance

**SQLpro** · 23/03/2006, 17h33

Bonjour,

ces requêtes, dites "d'agrégat de période temporelles" sont extrêment complexes à écrire.

je ne puis me lancer dans la mise au point d'une telle chose par Internet : manque de temps, manque de spécifications (lisez ceci : http://www.developpez.net/forums/viewtopic.php?t=32668)

Si vous n'avez pas besoin de performances, utilisez un curseur.

Si vous avez réellement besoin de performances, alors contactez moi.

A +

**Médiat** · 24/03/2006, 05h47

Essaye

Code :

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

1
2
3
4
SELECT s.num, to_char(MIN(date_entree),   'DD/MM/YYYY HH24:MI') debut, secteur, to_char(MAX(date_sortie),   'DD/MM/YYYY HH24:MI') fin
FROM statique s
GROUP BY num, secteur
ORDER BY num, debut

Le résultat sur ton jeu de test est correct, mais faux avec

Code :

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

1
2
3
4
5
2        13/02/05 17:15          sect1       13/02/05 19:25  
2        13/02/05 19:25          sect2       17/02/05 09:05  
2        17/02/05 09:05          sect3       17/02/05 16:50  
2        17/02/05 16:50          sect2       18/02/05 16:50  
2        18/02/05 16:50          sortie

Si ce cas peut se produire, il faut compliquer un peu :

Code :

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

1
2
3
4
5
6
7
8
9
10
select num, to_char(min(date_entree),   'DD/MM/YYYY HH24:MI') debut, secteur, to_char(max(date_sortie),   'DD/MM/YYYY HH24:MI') fin
from
(select s.*, sys_connect_by_path(to_char(num, 'FM000')|| to_char(date_entree, 'YYYYMMDDHH24MI'), ' ') as chemin
 from statique s
 connect by num = prior num and secteur = prior secteur and date_entree = prior date_sortie
 start with (num, secteur, date_entree) in (select num, secteur, date_entree
                                           from statique a
                                           where not exists (select null from statique b where a.num = b.num and a.secteur = b.secteur and b.date_sortie = a.date_entree )))
group by num, secteur, substr(chemin, 1, 16)
order by num, debut

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
1	13/02/2005 14:15	sect1	13/02/2005 16:25
1	13/02/2005 16:25	sect2	14/02/2005 16:50
1	14/02/2005 16:50	sortie	(null)
2	13/02/2005 17:15	sect1	13/02/2005 19:25
2	13/02/2005 19:25	sect2	17/02/2005 09:05
2	17/02/2005 09:05	sect3	17/02/2005 16:50
2	17/02/2005 16:50	sect2	18/02/2005 16:50
2	18/02/2005 16:50	sortie	(null)
3	10/01/2005 17:15	sect1	13/01/2005 19:25
3	13/01/2005 19:25	sect2	17/01/2005 16:55
3	17/01/2005 16:55	sortie	(null)

Ce n'est pas très clean, mais je n'ai pas le temps de formater, c'est bien sur, spécifique ORACLE (sinon, il faut utiliser WITH [RECURSIVE] (avec SQLServer 2005 par exemple), et ce serait plus simple et plus élégant).

sys_connect_by_path est officiel depuis ORACLE 9i, mais fonctionne avec la 8i (non supportée, donc attention ...)

**SQLpro** · 24/03/2006, 11h31

Bonjour,

Mediat il existe des solutions sans aucune récursivité même s'il y a chevauchement des périodes.
La récursivité, même en SQL n'étant franchement pas performante.

Mais ces requêtes sont très complexes à mettre au point de façon optimisées.

A +

**nizniz** · 24/03/2006, 14h46

j'ai tester la 2 eme requête et sa ne marche pas.
j'obtient des dates de sorties et des dates d'entrees identiques sur toutes les lignes.
je vais essayé d'utiliser un curseur et un tableau dans une proc en PLSQL

**Médiat** · 24/03/2006, 17h53

Envoyé par nizniz

j'ai tester la 2 eme requête et sa ne marche pas.
j'obtient des dates de sorties et des dates d'entrees identiques sur toutes les lignes.
je vais essayé d'utiliser un curseur et un tableau dans une proc en PLSQL

Le résultat que j'ai publié est celui de ma requête, un simple copier-coller de la fenêtre "Results", donc je suppose que tu n'as pas exécuté la même requête...

Peux-tu copier ici la requête que tu as essayé (éventuellement avec les données que tu as dans la table (sous forme d'INSERT)).

**nizniz** · 25/03/2006, 16h09

bonjour,
je voulais crée directement la table en inserant la premiere requete (create table statique ...) dans le 2eme code que tu ma proposé.
alors, j'ai supprimé la premiere ligne de code de la 1er requete(create table statique as) et j'ai gardé que la selection.
et dans un 2eme temps, j'ai remplacé chaque appel a la table statique dans ton code par ma premiere requete modifié, et j'ai ajouté le create table statique as au début de ton code.
au final, je me suis trouvé avec une requete qui fait ~ 25 lignes!

[Oracle] calcul de date de sortie et de la durée de séjour

Langage SQL

Discussions similaires

Partager

Partager