Ça parait simple mais !

**Just-Soft** · 10/12/2014, 10h17

Salut tout le monde
j'ai 4 tables :

tb_contrat {num_contrat, date_effet, PMD, code_tarif}
tb_tarif {code_tarif, PU_PMD}
tb_energie {num_facture, num_contrat, code_STEP}
tb_STEP {code_STEP, nom_STEP}

à chaque STEP (ouvrage) est associé un contrat de fourniture d'énergie électrique, et pour chaque facture on doit être sûr qu'on ramène les bonnes informations correspondantes au contrat en vigueur selon la date de la facture. Donc la date de la facture doit correspondre au contrat approprié. Ce qui revient à dire qu'une facture est régie par un seul contrat même si l'ouvrage (STEP) a plusieurs contrats.

Par exemple :
contrat 1 : numéro 628526 date effet 10/01/2007
contrat 2 : numéro 628526 date effet 15/07/2010
contrat 3 : numéro 628526 date effet 13/09/2014

facture 1 : 05/02/2007 ---> contrat 1
facture 2 : 09/04/2009 ---> contrat 2
facture 3 : 11/09/2010 ---> contrat 2
facture 4 : 20/11/2012 ---> contrat 2
facture 5 : 08/12/2014 ---> contrat 3

mon idée est de chercher la max(date_effet) de la table des contrats pour l'ouvrage en question puis de le comparer à la date de la facture, si date_facture > date_effet alors c'est le bon contrat sinon éliminer le dernier contrat et rechercher le max puis comparer jusqu'à satisfaction de la condition ....

Toute autre idée est la bienvenue.

un bout de code pour commencer :

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
select 
    s.nom_step,
    (select code_tarif from tb_contrat where e.date_mois >
     (select max(date_effet) from tb_contrat
      inner join tb_step on (tb_step.code_step = tb_contrat.code_step)
       where s.code_step = tb_step.code_step)) ,
    e.num_facture,
    e.date_mois,
    c.pmd
from tb_contrat c
   inner join tb_step s on (c.code_step = s.code_step)
   inner join tb_energie e on (s.code_step = e.code_step)
   inner join tb_tarif t on (c.code_tarif = t.code_tarif)
where 
   (
      (s.code_step = 8)
   and 
      (e.date_mois = :date_facture)
   )

le résultat est erroné puisque j'ai à la sortie plusieurs lignes à la place d'une seule comme je le souhaite.

Merci par avance.

**aieeeuuuuu** · 10/12/2014, 11h49

Bonjour,

Il semble manquer des colonnes dans la description de vos tables (e.date_mois par exemple).

Cependant je ne comprend pas bien votre problème avec les dates. Vous avez une clef étrangère tb_energie référançant un contrat. Vous savez donc à quelle contrat une facture fait référence...

**Just-Soft** · 10/12/2014, 16h48

Envoyé par aieeeuuuuu

Bonjour,

Il semble manquer des colonnes dans la description de vos tables (e.date_mois par exemple).

bien vue, s'était un peu volontaire car j'ai pas donné toute les colonnes bien que celle-ci soit importante. Tout à fait la table énergie contient un champ (date_mois) qui fait référence à la date de la facture.

Envoyé par aieeeuuuuu

Cependant je ne comprend pas bien votre problème avec les dates. Vous avez une clef étrangère tb_energie référançant un contrat. Vous savez donc à quelle contrat une facture fait référence...

bien vue encore mais hélas le numéro de contrat ne change pas même si le contrat change donc cette clé ne sert pas à avoir la période du contrat qui régit la facture.

**SergioMaster** · 11/12/2014, 08h15

Bonjour,

et si , on avait un script pour créer les tables (même sans tous les champs) et un jeu d'essai ainsi que ce qu'il doit être prévu de récupérer en fonction de ce dernier ? Bref si tu appliquais les recommandations générales pour poser une question SQL bref ce qui est indiqué dans http://www.developpez.net/forums/a69...gage-sql-lire/ (points 5 et 6) cela serait franchement plus facile (en tout cas pour moi!) .

L'énoncé est déjà pas simple , il y manque des informations , alors si en plus il faut faire l'effort de se taper la création des tables et du jeu d'essai tu comprendras que l'on (je) n'y saute pas à pieds joints !

le numéro de contrat ne change pas même si le contrat change donc cette clé ne sert pas à avoir la période du contrat qui régit la facture.

donc tu nous encombres avec une donnée inutile ?

Bon , après ce "coup de gueule modéré" , moi déjà , j'aime pas un select dans un select dans un select

Code :

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

1
2
3
4
5
 Select ...   (select code_tarif from tb_contrat where e.date_mois >
     (select max(date_effet) from tb_contrat
      inner join tb_step on (tb_step.code_step = tb_contrat.code_step)
       where s.code_step = tb_step.code_step)),
...

rien de tel pour y perdre son chemin en route , pourquoi ne pas en faire une , des, CTE(s) ?

**Just-Soft** · 11/12/2014, 10h03

Envoyé par SergioMaster

Bonjour,

et si , on avait un script pour créer les tables (même sans tous les champs) et un jeu d'essai ainsi que ce qu'il doit être prévu de récupérer en fonction de ce dernier ? Bref si tu appliquais les recommandations générales pour poser une question SQL bref ce qui est indiqué dans http://www.developpez.net/forums/a69...gage-sql-lire/ (points 5 et 6) cela serait franchement plus facile (en tout cas pour moi!) .

L'énoncé est déjà pas simple , il y manque des informations , alors si en plus il faut faire l'effort de se taper la création des tables et du jeu d'essai tu comprendras que l'on (je) n'y saute pas à pieds joints !

donc tu nous encombres avec une donnée inutile ?

Bon , après ce "coup de gueule modéré"?

Si vous le dites ... de ma part je n'avais nullement l'intention de vous "gueuler" mais bon .... mon commentaire pour tout cela ne serait que "WAW"

Envoyé par SergioMaster

j'aime pas un select dans un select dans un select

Code :

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

1
2
3
4
5
 Select ...   (select code_tarif from tb_contrat where e.date_mois >
     (select max(date_effet) from tb_contrat
      inner join tb_step on (tb_step.code_step = tb_contrat.code_step)
       where s.code_step = tb_step.code_step)),
...

rien de tel pour y perdre son chemin en route , pourquoi ne pas en faire une , des, CTE(s) ?

OK pour la proposition maitre .... même si j'ai du mal à construire une CTE d'autant plus que j'avais espérer un correction de mon code ou une proposition en SQL

**SergioMaster** · 11/12/2014, 10h08

Envoyé par Just-Soft

Si vous le dites ... de ma part je n'avais nullement l'intention de vous "gueuler" mais bon ....

c'est le mien de "coup de gueule" , je suis flemmard (comme tout bon vieil informaticien qui se respecte), j'aime avoir toutes les billes en main avant de jouer

j'avais espérer un correction de mon code ou une proposition en SQL

"correction tu auras quand script tu donneras" aurais dit maitre yoda

**Just-Soft** · 11/12/2014, 10h47

Ok maître YODA ...

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
CREATE TABLE TB_TARIF (
    CODE_TARIF                 VARCHAR(3) NOT NULL,
    DATE_DECISION              DATE,
    REDEVANCE_FIXE             FLOAT,
    PMD_PU                     FLOAT,
    PMA_PU                     FLOAT
);
 
CREATE TABLE TB_CONTRAT (
    NUM_CLIENT        VARCHAR(15) NOT NULL,
    CODE_STEP         SMALLINT,
    CODE_TARIF        VARCHAR(3),
    DATE_EFFET        DATE DEFAULT '01.01.1990' NOT NULL,
    DUREE_CONTRAT     SMALLINT,
    PMD               SMALLINT
);
 
CREATE TABLE TB_STEP (
    CODE_STEP                 SMALLINT NOT NULL,
    NUM_CLIENT                VARCHAR(12),
    NOM_STEP                  VARCHAR(20) NOT NULL
);
 
CREATE TABLE TB_ENERGIE (
    NUM_FACTURE             VARCHAR(12) NOT NULL,
    DATE_MOIS               DATE NOT NULL,
    CODE_STEP               INTEGER,
    NUM_CLIENT              VARCHAR(7)
);

et pour l'essai voici les "records"
table TARIF

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
INSERT INTO TB_TARIF (CODE_TARIF, DATE_DECISION, REDEVANCE_FIXE, PMD_PU, PMA_PU)
              VALUES ('E31', '2005-12-01', 421177.72, 31.61);
INSERT INTO TB_TARIF (CODE_TARIF, DATE_DECISION, REDEVANCE_FIXE, PMD_PU, PMA_PU)
              VALUES ('E42', '2005-12-01', 429.71, 32.25, 150.48);
INSERT INTO TB_TARIF (CODE_TARIF, DATE_DECISION, REDEVANCE_FIXE, PMD_PU, PMA_PU)
              VALUES ('E41', '2005-12-01', 32227.79, 21.54, 96.79);
INSERT INTO TB_TARIF (CODE_TARIF, DATE_DECISION, REDEVANCE_FIXE, PMD_PU, PMA_PU)
              VALUES ('E43', '2005-12-01', 429.71, 32.25, 128.80);
INSERT INTO TB_TARIF (CODE_TARIF, DATE_DECISION, REDEVANCE_FIXE, PMD_PU, PMA_PU)
              VALUES ('E44', '2005-12-01', 429.71, 32.25, 150.48);

table CONTRAT

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
 
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6191556', 7, 'E41', '2009-12-23', 5, 1500);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6495842', NULL, 'E43', '2009-02-24', 5, 80);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6190801', NULL, 'E42', '2009-05-02', 5, 80);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6190763', NULL, 'E42', '2006-11-04', 5, 120);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6190802', NULL, 'E42', '2009-05-02', 5, 80);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6190848', NULL, 'E42', '2006-04-01', 5, 80);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6594079', NULL, 'E43', '2007-01-27', 5, 120);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6594010', NULL, 'E43', '2000-01-01', NULL, 80);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6594105', 9, 'E42', '2000-01-02', 5, 500);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6594104', NULL, 'E42', '2000-01-03', 5, 320);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6495826', 8, 'E42', '2012-03-27', 5, 500);
INSERT INTO TB_CONTRAT (NUM_CLIENT, CODE_STEP, CODE_TARIF, DATE_EFFET, DUREE_CONTRAT, PMD)
                VALUES ('6495826', 8, 'E42', '2010-04-10', 5, 1000);

table STEP

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO TB_STEP (CODE_STEP, NUM_CLIENT, NOM_STEP)
             VALUES (7, '6191556', 'CHLEF');
INSERT INTO TB_STEP (CODE_STEP, NUM_CLIENT, NOM_STEP)
             VALUES (8, '6495826', 'AIN DEFLA');
INSERT INTO TB_STEP (CODE_STEP, NUM_CLIENT, NOM_STEP)
             VALUES (9, '6594105', 'AMMI MOUSSA');
INSERT INTO TB_STEP (CODE_STEP, NUM_CLIENT, NOM_STEP)
             VALUES (10, '6593524', 'SIDI LAZREG');

table ENERGIE

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
42
43
44
45
46
47
48
49
50
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641012001298', '2010-12-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641103004114', '2011-03-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641104005123', '2011-04-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641009001564', '2010-09-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641006000488', '2010-06-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641003001522', '2010-03-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641005003445', '2010-05-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641007001489', '2010-07-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641001003419', '2010-01-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640901022905', '2009-01-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640903024726', '2009-03-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640905026122', '2009-05-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640908029268', '2009-08-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640911001345', '2009-11-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641106007146', '2011-06-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640912002296', '2009-12-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('640909030126', '2009-09-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641105006132', '2011-05-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641108009192', '2011-08-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641107008169', '2011-07-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641109000604', '2011-09-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641110001637', '2011-10-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641111000607', '2011-11-30', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641407000667', '2014-07-31', 8);
INSERT INTO TB_ENERGIE (NUM_FACTURE, DATE_MOIS, CODE_STEP)
                VALUES ('641410001604', '2014-10-31', 8);

en espérant que cela aidera ... n'hésiter surtout pas si je dois compléter mes info

merci par avance

**SergioMaster** · 11/12/2014, 10h49

[Edit] le jeu d'essai n'est pas bon pour la table TARIF

Insert 5 champs , 4 valeurs pour la première ligne , enfin peu importe j'ai mis une valeur bidon
et le résultat souhaité ?

**Just-Soft** · 11/12/2014, 11h10

Envoyé par SergioMaster

[Edit] le jeu d'essai n'est pas bon pour la table TARIF

Insert 5 champs , 4 valeurs pour la première ligne , enfin peu importe j'ai mis une valeur bidon
et le résultat souhaité ?

maalich comme on dit chez nous ... le champ en question n'a pas trop d'importance pour la requête souhaitée donc vous pouvez vous en passer maître

**SergioMaster** · 11/12/2014, 11h25

maalich

tiens , c'est manarf en marocain (je suis pas sur de l'orthographe par contre)

est-ce que le résultat souhaité est ?

code_sep date_contrat site tarif date max Facture date
8 27.03.2012 AIN DEFLA E42 27.03.2012 641410001604 31.10.2014 500

si oui , j'ai fait ainsi en partant du code fourni

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
WITH CTE AS (
  SELECT c.code_step,max(c.DATE_EFFET) AS DE
      FROM tb_contrat c 
           JOIN tb_step s on c.CODE_STEP=s.CODE_STEP
      group by 1
 
  )
 
select FIRST 1
    s.CODE_STEP,
    c.DATE_EFFET, 
    s.nom_step,
    c.code_tarif,
    cte.de,
    e.num_facture,
    e.date_mois,
    c.pmd
from tb_contrat c
   join cte on c.CODE_STEP=cte.code_step
   join tb_step s on (c.code_step = s.code_step)
   join tb_energie e on (s.code_step = e.code_step)
where e.DATE_MOIS>=cte.de
order by e.date_mois desc

il y a certainement plus simple à mon avis , il vaudrait mieux partir des factures

mon idée est de chercher la max(date_effet) de la table des contrats pour l'ouvrage en question puis de le comparer à la date de la facture, si date_facture > date_effet alors c'est le bon contrat sinon éliminer le dernier contrat et rechercher le max puis comparer jusqu'à satisfaction de la condition ....

moi, je dirais je prend la dernière facture , et je la lie au dernier contrat ? mais ai-je bien compris ?

**SergioMaster** · 11/12/2014, 11h45

une autre solution

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
 WITH -- récupère dernière facture STEP 
        CTEF AS ( SELECT E.CODE_STEP,MAX(E.DATE_MOIS) AS DATE_FACTURE,MAX(E.NUM_FACTURE) AS NUMERO_FACTURE FROM TB_ENERGIE E 
                 GROUP BY E.CODE_STEP),
       -- récupère dernier contrat STEP 
       CTEC AS ( SELECT c.CODE_STEP,MAX(C.DATE_EFFET) AS DATE_CONTRAT FROM  TB_CONTRAT C
                 GROUP BY C.CODE_STEP)
 -- Jointure des deux et récupération d'autres infos                 
 SELECT CTEF.*,CTEC.*,c.CODE_TARIF,T.PMA_PU 
     FROM CTEF JOIN CTEC ON CTEF.CODE_STEP=CTEC.CODE_STEP
               JOIN TB_CONTRAT C ON CTEC.CODE_STEP=C.CODE_STEP AND CTEC.DATE_CONTRAT=C.DATE_EFFET                
               JOIN TB_TARIF T ON C.CODE_TARIF=T.CODE_TARIF

résultat

CODE_STEP DATE_FACTURE NUMERO_FACTURE CODE_STEP DATE_CONTRAT CODE_TARIF PMA_PU
8 31.10.2014 641410001604 8 27.03.2012 E42 150.479996

**Just-Soft** · 11/12/2014, 13h27

maalich = c'est pas grave
manaaraf = je ne sais pas

pour le code SQL fournie, malheureusement il ne donne pas le bon résultat ....
Je m'explique, je voudrais faire des simulations de calcul pour toutes les factures, pour les STEP qui n'ont qu'un seul contrat d'énergie la simulation est bonne, par contre pour les ouvrages disposant de plus d'un contrat, il me faudra au préalable savoir quel contrat régie la facture en question donc le dernier contrat n'est pas le bon pour une facture qui a était établie il ya 5 ans p.e et qui était régie par un contrat d'une autre date de validité.

**SergioMaster** · 11/12/2014, 15h21

donc si je reformule : savoir quel contrat s'applique à une facture ?

**Just-Soft** · 11/12/2014, 16h28

Envoyé par SergioMaster

donc si je reformule : savoir quel contrat s'applique à une facture ?

oui oui oui c'est bien ça ...

**SergioMaster** · 11/12/2014, 17h24

Bon , alors Quid d'une petite procédure ?

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
 
SET TERM ^ ;
CREATE PROCEDURE FACTURE_CONTRAT
RETURNS (
    NUM_FACT Varchar(12),
    DATE_FACT Date,
    STEP Smallint,
    TARIF Varchar(3),
    EFFET Date )
AS
BEGIN
 FOR SELECT NUM_FACTURE,DATE_MOIS,CODE_STEP 
     FROM TB_ENERGIE INTO :NUM_FACT,:DATE_FACT,:STEP 
     DO BEGIN
       SELECT FIRST 1 CODE_TARIF,DATE_EFFET FROM TB_CONTRAT 
       WHERE CODE_STEP=:STEP AND DATE_EFFET>=:DATE_FACT  
       ORDER BY DATE_EFFET
       INTO :TARIF,:EFFET;
       SUSPEND; 
     END
END^
SET TERM ; ^

à peaufiner bien sûr , peut être avec le num_client etc...

et après un petit

Code :

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

  SELECT * FROM FACTURE_CONTRAT ORDER BY DATE_FACT

par exemple

Ça parait simple mais !

Firebird

Discussions similaires

Partager

Partager