[8i]Aide sur Requête - différence ROWNUM N - ROWNUM N +1

**jdotti** · 28/11/2006, 11h57

Bonjour,

Le sujet du topic est on ne peut plus classique mais j'en ai mal au crane tellement cette requête m'a pris la tête.

Il s'agit d'une requête ramenant des compteurs d'eau pour des logements, le tarrif qui est appliqué à la consomation d'eau pour une période, ainsi que la relevé compteur pour la période.

La voici donc :

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
SELECT ROWNUM "NUMLIGNE",
       KCPCPT.LKLO "MODULE",
       TO_CHAR(KCNTLOC.CDECL,'DD-MM-YYYY')  "ENTREE",
       TO_CHAR(KCNTLOC.CDSCL,'DD-MM-YYYY') "SORTIE",
       KCPIND.CNCT "CONTRAT",
       KCNT.CNDCT "LOCATAIRE",
       KCPCPT.CPIDF "NUM CPT", 
       DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
       DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
       TO_CHAR(KCPIND.CODP,'YYYY-MM-DD') "DATE RELEVE",
       DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
       (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                      KTARCPT.TATARIF) FROM KTARCPT 
        WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL )
              AND (KTARCPT.TCTC=KCPCPT.TCTC)
              AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC) AND (KTARCPT2.TADATVIG <= KCPIND.CODP))
              )        
        ) "P.U"
        FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
        WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
        AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
        AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
        AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
        AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
        AND (KLOGEMT.SCSO='PU')
        AND (KLOGEMT.ATC IS NOT NULL)
        AND (KCPIND.CODP >= '&&param1') 
        ORDER BY 1,4,6, KCPIND.CODP DESC

Ce que je souhaiterais obtenir c'est un résultat intégrant non pas le relevé, mais la consomation , c'est à dire la différence entre :

Code :

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

1
2
("INDEX RELEVE" pour ROWNUM = 1 ) - ( "INDEX RELEVE" pour ROWNUM = 2 )
SI "MODULE"="MODULE" ET "CONTRAT"="CONTRAT" ET "NUM CPT"="NUM CPT"

J'ai bien essayé en passant par une réquête affreuse en jouant sur les dates et le ROWNUM du genre :

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SELECT "NUMLIGNE",
       "MODULE",
       "ENTREE",
       "SORTIE",
       "CONTRAT",
       "DATE RELEVE",
       (SELECT "DATA2"."INDEX RELEVE" 
        FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
                     KCPIND.CNCT "CONTRAT",
             KCNT.CNDCT "LOCATAIRE",
             KCPCPT.CPIDF "NUM CPT", 
             DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
             DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
             KCPIND.CODP "DATE RELEVE",
             DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
             (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                            KTARCPT.TATARIF) FROM KTARCPT 
              WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL)
                    AND (KTARCPT.TCTC=KCPCPT.TCTC)
                    AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
                    )        
              ) "P.U",
              DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
              FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
              WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
              AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
              AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
              AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
              AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
              AND (KLOGEMT.SCSO='PU')
              AND (KLOGEMT.ATC IS NOT NULL)
              AND (KCPIND.CODP >= '01/07/2005') 
              ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA2"
        WHERE ("DATA"."MODULE"="DATA2"."MODULE" AND "DATA"."CONTRAT"="DATA2"."CONTRAT")
          AND (("DATA"."NUMLIGNE"+ 1) ="DATA2"."NUMLIGNE") )  
          ||' - ' || 
          (SELECT "DATA3"."INDEX RELEVE" 
        FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
                     KCPIND.CNCT "CONTRAT",
             KCNT.CNDCT "LOCATAIRE",
             KCPCPT.CPIDF "NUM CPT", 
             DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
             DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
             KCPIND.CODP "DATE RELEVE",
             DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
             (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                            KTARCPT.TATARIF) FROM KTARCPT 
              WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL)
                    AND (KTARCPT.TCTC=KCPCPT.TCTC)
                    AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
                    )        
              ) "P.U",
              DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
              FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
              WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
              AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
              AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
              AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
              AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
              AND (KLOGEMT.SCSO='PU')
              AND (KLOGEMT.ATC IS NOT NULL)
              AND (KCPIND.CODP >= '01/07/2005') 
              ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA3"
        WHERE ("DATA"."MODULE"="DATA3"."MODULE" AND "DATA"."CONTRAT"="DATA3"."CONTRAT")
          AND ("DATA"."NUMLIGNE"="DATA3"."NUMLIGNE") ) "CONSO"
FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
       TO_CHAR(KCNTLOC.CDECL,'DD-MM-YYYY')  "ENTREE",
       TO_CHAR(KCNTLOC.CDSCL,'DD-MM-YYYY') "SORTIE",
       KCPIND.CNCT "CONTRAT",
       KCNT.CNDCT "LOCATAIRE",
       KCPCPT.CPIDF "NUM CPT", 
       DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
       DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
       KCPIND.CODP "DATE RELEVE",
       DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
       (SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
                      KTARCPT.TATARIF) FROM KTARCPT 
        WHERE (KTARCPT.PKPR=KLOGEMT.PKPR  OR KTARCPT.PKPR IS NULL)
              AND (KTARCPT.TCTC=KCPCPT.TCTC)
              AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
              )        
        ) "P.U",
        DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
        FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
        WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
        AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
        AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
        AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
        AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
        AND (KLOGEMT.SCSO='PU')
        AND (KLOGEMT.ATC IS NOT NULL)
        AND (KCPIND.CODP >= '01/07/2005') 
        ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA"
WHERE "DATA"."DATE RELEVE" >= TO_DATE('2005-11-01','YYYY-MM-DD')

Mais en plus d'être affreuse, cette requête ne me raméne pas le bon résultat...

Bon , vu la spécificité de ma demande, je n'en voudrais à personne de ne pas y répondre

, en revanche , je remercie d'avance toutes personnes qui aura eut le courage de s'y pencher !

Merci donc et @+

**nuke_y** · 28/11/2006, 12h07

Je crois que ce dont tu as besoin ce sont les fonctions analytiques.

Je reste te soutenir moralement en attendant qu'un fortiche de ce domaine passe te donner la solution.

**nuke_y** · 28/11/2006, 12h11

Tu pourrais donner un jeu de test simple et une vision de ce que tu voudrais obtenir ?

**jdotti** · 28/11/2006, 12h34

Salut nuke_y et merci pour tes réponses ...

Va pour un jeu de test simple
.
Voici donc ce que me donne la requête ramenant les données :

ROWNUM / MODULE / CONTRAT / COMPTEUR / DATE RELEVE / INDEX RELEVE / PRIX UNITAIRE
- 1/ 0110011101 / 1 / CFROID2001 / 01-11-2006 / 250 / 3€
- 2/ 0110011101 / 1 / CFROID2001 / 01-12-2006 / 200 / 3€
- ....
- 250/ 0110011101 / 1 / CFROID2001 / 01-11-2006 / 200 / 3€
- 251/ 0110011101 / 1 / CFROID2001 / 01-12-2006 / 100/ 3€

Et voici le résultat que je souhaite obtenir

ROWNUM / MODULE / CONTRAT / COMPTEUR / DATE RELEVE / CONSOMATION / COUT
- 1/ 0110011101 / 1 / CFROID2001 / 01-11-2006 / 50 / 150€
- ....
- 250/ 0110011101 / 1 / CFROID2001 / 01-11-2006 / 100 / 300€

Par contre, j'ai essayé d'utiliser les fonctions analytiques, mais elle ne passe pas ... J'obtiens un message d'erreur que je pourrais retrouver si besoin...

**nuke_y** · 28/11/2006, 12h46

Si les mois se suivent c'est très simple : tu fais une jointure de la table avec elle même et tu précises que DATE_RELEVE1 = (DATE_RELEVE2 - 1 mois) puis tu fais la soustraction entre les 2 INDEX_RELEVE.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT
T1.MODULE,
T1.CONTRAT,
T1.COMPTEUR,
(T1."INDEX RELEVE"-T2."INDEX RELEVE")*T1."PRIX UNITAIRE"
FROM
MATABLE T1, MATABLE T2
WHERE
T1.MODULE=T2.MODULE
AND T1.CONTRAT=T2.CONTRAT
AND T1.COMPTEUR=T2.COMPTEUR
AND T1."DATE RELEVE" = (T2."DATE RELEVE" - INTERVAL '1' MONTH)

Et ça devrait rouler. Maintenant si les périodes ne se suivent pas forcément...

**nuke_y** · 28/11/2006, 14h11

Mieux, mieux, j'ai mieux. Bon il y a ptet un problème de logique, vu que la valeur en décembre est plus faible que celle en novembre mais bon :

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
DROP TABLE EDF;
 
CREATE TABLE EDF (
MODULE NUMBER(36),
CONTRAT NUMBER(10),
COMPTEUR VARCHAR2(20),
DATE_RELEVE DATE,
INDEX_RELEVE NUMBER(8),
PRIX_UNITAIRE NUMBER(5)
);
 
INSERT INTO EDF (MODULE, CONTRAT, COMPTEUR, DATE_RELEVE, INDEX_RELEVE, PRIX_UNITAIRE) VALUES(0110011101,1,'CFROID2001','01/11/2006',250,3);
INSERT INTO EDF (MODULE, CONTRAT, COMPTEUR, DATE_RELEVE, INDEX_RELEVE, PRIX_UNITAIRE) VALUES(0110011101,1,'CFROID2001','01/12/2006',200,3);
INSERT INTO EDF (MODULE, CONTRAT, COMPTEUR, DATE_RELEVE, INDEX_RELEVE, PRIX_UNITAIRE) VALUES(0110011102,1,'CFROID2001','01/11/2006',200,3);
INSERT INTO EDF (MODULE, CONTRAT, COMPTEUR, DATE_RELEVE, INDEX_RELEVE, PRIX_UNITAIRE) VALUES(0110011102,1,'CFROID2001','01/12/2006',100,3);
 
select
MODULE,
CONTRAT,
COMPTEUR,
DATE_RELEVE,
INDEX_RELEVE,
PRIX_UNITAIRE,
lag(INDEX_RELEVE,1) over(partition by MODULE, CONTRAT, COMPTEUR ORDER BY DATE_RELEVE) "INDEX PREC",
INDEX_RELEVE - lag(INDEX_RELEVE,1) over(partition by MODULE, CONTRAT, COMPTEUR ORDER BY DATE_RELEVE) "DELTA PREC",
PRIX_UNITAIRE * (INDEX_RELEVE - lag(INDEX_RELEVE,1) over(partition by MODULE, CONTRAT, COMPTEUR ORDER BY DATE_RELEVE)) "DELTA PREC en EURO",
lead(INDEX_RELEVE,1) over(partition by MODULE, CONTRAT, COMPTEUR ORDER BY DATE_RELEVE) "INDEX SUIV",
INDEX_RELEVE - lead(INDEX_RELEVE,1) over(partition by MODULE, CONTRAT, COMPTEUR ORDER BY DATE_RELEVE) "DELTA SUIV",
PRIX_UNITAIRE * (INDEX_RELEVE - lead(INDEX_RELEVE,1) over(partition by MODULE, CONTRAT, COMPTEUR ORDER BY DATE_RELEVE)) "DELTA SUIV en EURO"
from EDF;

Et ça donne :

Code :

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

1
2
3
4
5
MODULE	CONTRAT	COMPTEUR	DATE_RELEVE	INDEX_RELEVE	PRIX_UNITAIRE
110011101	1	CFROID2001	01/11/2006	250	3
110011101	1	CFROID2001	01/12/2006	200	3
110011102	1	CFROID2001	01/11/2006	200	3
110011102	1	CFROID2001	01/12/2006	100	3

Code :

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

1
2
3
4
5
MODULE	CONTRAT	COMPTEUR	DATE_RELEVE	INDEX_RELEVE	PRIX_UNITAIRE	INDEX PREC	DELTA PREC	DELTA PREC en EURO	INDEX SUIV	DELTA SUIV	DELTA SUIV en EURO
110011101	1	CFROID2001	01/11/2006	250	3				200	50	150
110011101	1	CFROID2001	01/12/2006	200	3	250	-50	-150			
110011102	1	CFROID2001	01/11/2006	200	3				100	100	300
110011102	1	CFROID2001	01/12/2006	100	3	200	-100	-300

**plaineR** · 28/11/2006, 14h33

Envoyé par nuke_y

Je crois que ce dont tu as besoin ce sont les fonctions analytiques.

+1, à condition que tu sois en Entreprise edition (en 8i les fonctions analytiques ne sont disponibles pas pour la standard edition) :
http://lalystar.developpez.com/fonctionsAnalytiques/