Construction d'une requête imbriquée

[gudul]

Bonjour à tous, j'ai un problème de réflexion: j'ai des soucis pour faire une requête .

Je suis sous Firebird et j'ai une table qui contient les champs d'occupation.
dateDebutOccupation, duree, Emplacement, Occupation (m²)...


Je vous explique la situation: je gère des occupations de surfaces, le but de ma requete serait d'avoir l'occupation totale à une date donnée puis ensuite entre 2 dates.
J'ai fait un petit dessin pour être clair:


les flèches vertes sont les occupations (avec la surface occupée), A et B sont les dates qui définissent l'intervalle de temps, p1 à p6 sont les dates où l'occupation totale doit être calculée.
Mon idée est que pour avoir la surface maximale occupée entre les dates A et B, il faut que je fasse la somme des occupations de chacune des dates p1 à p6 + celles de A et B et prendre la somme la plus grande. J'ai déja les requetes pour ne sélectionner que les dates entre A et B mais je ne vois pas comment mixer le tout pour avoir ma selection du max de mes sommes.
J'espere que tout est clair , voilà mes 2 requetes en omettant l'emplacement:
Code :
[LEFT]SELECT dateDebutOccupation FROM Occupations WHERE dateDebutOccupation >=A ANDdateDebutOccupation <B[/LEFT]

Code :
[LEFT]SELECT (dateDebutOccupation+duree) FROM Occupations WHERE (dateDebutOccupation+duree)>A AND(dateDebutOccupation+duree)<B[/LEFT]


Voilà si vous avez des questions ou des idées... toute aide est la bienvenue car je suis pas loin de la pendaison
Merci

[Mystro]

Bonjour,

Je ne comprend pas bien tes requêtes par rapport à ce que tu cherche...

Occupation totale à une date donnée :

Code :

1
2
3
4
5
6
7
SELECT
      somme(Occupation)
  FROM
      Occupations
  WHERE
      dateDebutOccupation <= date_donnee
      AND dateDebutOccupation + duree >= date_donnee

Une petite question concernant l'occupation entre 2 dates, faut il ne considérer que les occupations qui sont entièrement contenues entre les 2 dates ou bien il faut aussi comptabiliser les occupations qui sont à chevale sur l'une ou l'autre ou les deux dates ?

[gudul]

La requête que tu as ecrit est celle pour une date donnée. Elle correspond a celle que j'ai hormis un >=.

Code :

1
2
3
4
5
6
7
8
[LEFT]Select
      somme(Occupation)
  from
      Occupations
  where
      dateDebutOccupation <= date_donnee
      and dateDebutOccupation + duree > date_donnee[/LEFT]

Les 2 requêtes que j'ai ecrite donnent les dates ou il y a un changement d'occupation entre les 2 dates. En faisant un UNION, j'obtient donc l'ensemble des dates où il faut faire les sommes d'occupation. Il faut aussi faire la somme d'occupation pour les 2 dates A et B. On obtient donc l'occupation pour les dates A, p1, p2, p3, p4, p5, p6, B. Il ne reste plus qu'a remonter le max de ces occupations.
L'exercice étant d'arriver à faire l'ensemble en une seule requête .

[gudul]

La requête qui remonte les dates retourne p1 à p6.
la requête qui donne l'occupation doit remonter l'ensemble des enregistrements:
A: 250 m²
p1: 200 m²
p2: 300 m²
p3: 250 m²
p4: 150 m²
p5: 180 m²
p6: 100 m²
B: 100 m²

l'occupation à la date B n'est pas nécessaire car elle correspond à l'occuption p6.

il faut que la requête me remonte 300 m².

[Médiat]

La requête ci-dessous est testée sous ORACLE :

Date de début = 15/01/2005
Date de Fin = 10/06/2005

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT max(sum(Occupation))
FROM dvp_Occupation INNER JOIN (SELECT dateDebutOccupation AS Borne
                                FROM  dvp_occupation
                                WHERE dateDebutOccupation >= to_date('15/01/2005', 'DD/MM/YYYY')
                                  AND dateDebutOccupation  < to_date('10/06/2005', 'DD/MM/YYYY')
                             union
                                SELECT dateDebutOccupation+duree
                                FROM  dvp_occupation
                                WHERE dateDebutOccupation + duree >= to_date('15/01/2005', 'DD/MM/YYYY')
                                  AND dateDebutOccupation + duree < to_date('10/06/2005', 'DD/MM/YYYY')
                             union
                                SELECT to_date('15/01/2005', 'DD/MM/YYYY')
                                FROM dual) a
                          ON  dateDebutOccupation <= Borne
                          AND dateDebutOccupation + Duree > Borne
GROUP BY Borne;

to_date est facile à comprendre et à transcrire si nécessaire ; le select to_date('15/01/2005', 'DD/MM/YYYY') from dual permet d'ajouter la ligne avec la date de départ, il doit y avoir l'équivalent avec FireBird, sinon il faut ajouter une union, tiens-moi au courant...

[gudul]

Merci de vous être penché sur la question je vais tester sous firebird. Je ne suis pas sûr qu'il accepte une sous-requête dans un inner join, je vous tiens au courant.

[gudul]

FireBird 1.5.3 n'accepte pas le select dans la clause from . (Erreur de syntax)
Y a-t-il un moyen de faire passer le prédicat dans la clause where ?

On est pas loin. Il faut aussi que je regarde un équivalent à from dual.

[Alexandre T]

En même temps, si vous postiez dans le forum Firebird, vous auriez plus de réponses !

Je déplace.

[TMuet]

Envoyé par gudul

Il faut aussi que je regarde un équivalent à from dual.

from rdb$database

[gudul]

merci, je me doutais bien que c'était rdb$database.

Est il possible de mettre un select dans la clause "from" ?
si non, la version firebird 2.0 corrige-t-elle ce manque ?

comment pouvoir réecrire la requête faite sous Oracle afin qu'elle fonctionne sous firebird?

[makowski]

Envoyé par gudul

merci, je me doutais bien que c'était rdb$database.
si non, la version firebird 2.0 corrige-t-elle ce manque ?

oui

[makowski]

Envoyé par gudul

comment pouvoir réecrire la requête faite sous Oracle afin qu'elle fonctionne sous firebird?

C'est possible avec si tu crée une procédure stockée
vite fait comme ça tu fait une procédure stockée qui ramène les bornes (en fait l'équivalent du select qui est dans le from dans la version oracle
puis ensuite tu fait ta requête qui ramène ta surface

ce qui donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SET TERM ^^ ;
CREATE PROCEDURE P_OCCUPATIONS (
  DEBUT Date,
  FIN Date)
 returns (
  BORNE Date) AS
begin
  FOR SELECT CAST(dateDebutOccupation AS DATE) FROM TB_Occupations
      WHERE dateDebutOccupation >=:DEBUT
      AND dateDebutOccupation <:FIN
      UNION
      SELECT CAST((dateDebutOccupation+duree) AS DATE)
      FROM TB_Occupations WHERE CAST((dateDebutOccupation+duree) AS DATE)>:DEBUT
      AND CAST((dateDebutOccupation+duree) AS DATE)<:FIN
      INTO :BORNE
   DO
   SUSPEND;
end^^
SET TERM ; ^^

ensuite tu utilises la procédure comme cela :

Code :

1
2
3
4
5
6
7
 
SELECT FIRST 1 sum(Occupation)
 FROM TB_OCCUPATIONS INNER JOIN
  P_OCCUPATIONS ('01/15/2005','06/10/2005') ON  dateDebutOccupation <= Borne
  AND dateDebutOccupation + Duree > Borne
GROUP BY Borne
ORDER BY 1 DESC;

[gudul]

merci, j'essai ca tout de suite et je vous tiens au courant

[gudul]

apres installation de firebird2, la requete faite sur le modèle d'oracle fonctionne sauf le max.
(Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Nested aggregate functions are not allowed.)

la requête utilisant le procedure stockée fonctionne aussi. je n'ai pas l'occupation pour la date de début et le max ne passe pas non plus.

[makowski]

Envoyé par gudul

apres installation de firebird2, la requete faite sur le modèle d'oracle fonctionne sauf le max.
(Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Nested aggregate functions are not allowed.)

la requête utilisant le procedure stockée fonctionne aussi. je n'ai pas l'occupation pour la date de début et le max ne passe pas non plus.

avec FIRST 1 et ORDER BY ... DESC, pas besoin du max
et s'il te manque la date du début, modifie la procédure stockée pour l'inclure, je l'ai fait vite fait sur le gaz, mais tu as là tous les éléments necessaires.

[gudul]

Merci pour tout. cela fonctionne très bien.

[gudul]

J'ai encore un problème . La requête ne me remonte pas la date et l'occupation quand celle ci est à 0.

La procédure stoquée me remonte bien les dates où il y a un changement d'occupation, dont la dernière date ou l'occupation passe à 0 mais dans la requête, je n'ai pas la date d'occupation avec une surface à null. (j'ai mis un right outer join sans grand succes)

[gudul]

oups je me suis trompé de requête

Code :

1
2
3
4
5
SELECT BORNE, occupsurface.f_occupoccupsurface
FROM occupsurface RIGHT OUTER JOIN
  P_OCCUPATIONS ('01/25/2007','02/28/2007') ON  occupsurface.d_dateoccupsurface <= Borne
  AND occupsurface.d_dateoccupsurface + occupsurface.f_dureeoccupsurface > Borne
GROUP BY Borne

voici la procedure qui me revoit ce qu'il faut.

Code :

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
SET TERM ^ ;
 
CREATE PROCEDURE P_OCCUPATIONS (
    DEBUT DATE,
    FIN DATE
RETURNS (
    BORNE DATE)
AS
begin
  SELECT :debut FROM rdb$database INTO :borne;
  SUSPEND;
 
  FOR SELECT CAST(occupsurface.d_dateoccupsurface AS DATE) FROM occupsurface
      WHERE occupsurface.d_dateoccupsurface >:DEBUT
        AND occupsurface.d_dateoccupsurface <:FIN
      UNION
      SELECT CAST((occupsurface.d_dateoccupsurface + occupsurface.f_dureeoccupsurface) AS DATE)
      FROM occupsurface WHERE CAST((occupsurface.d_dateoccupsurface + occupsurface.f_dureeoccupsurface) AS DATE)>:DEBUT
        AND CAST((occupsurface.d_dateoccupsurface + occupsurface.f_dureeoccupsurface) AS DATE)<:FIN
      INTO :BORNE
   DO
   SUSPEND;
end^
 
SET TERM ; ^

[gudul]

J'ai trouvé une solution de secours avec vos réponses. En fait je fais une requête qui me ramène toutes les dates pertinentes puis pour chaque date je vais chercher en base l'occupation. Pas top pour l'accès au SGBD mais comme le serveur et l'application sont sur la même machine, ça tient la route pour le moment.

à tous.



P.S: Je mets pas le sujet en résolu car je cherche encore quelques suggestions. Je repasserais régulièrement.

[makowski]

Envoyé par gudul

J'ai encore un problème . La requête ne me remonte pas la date et l'occupation quand celle ci est à 0.

La procédure stoquée me remonte bien les dates où il y a un changement d'occupation, dont la dernière date ou l'occupation passe à 0 mais dans la requête, je n'ai pas la date d'occupation avec une surface à null. (j'ai mis un right outer join sans grand succes)

en quoi tu as besoin de ces infos ?
je n'en vois pas l'intérêt au vu de ta demande