Faire un calcul par lignes par client selon le type

**TsunadeSama** · 03/11/2021, 13h53

Bonjour,
Je ne savais pas trop comment appelé cette discussion. Merci à ceux qui sont venu malgré tout.
J'ai fait appel à vos service il y a peu de temps pour m'aider à faire une requête pour extraire les 2 dernières dates les plus récentes.
Je pensais ensuite travailler mon fichier grâce à des fonctions EXCEL mais je n'y arrive pas. Je reviens donc vers vous pour savoir si il est possible de tout faire directement en SQL.

Ci-joint, la requête je j'utilise:

Code sql :

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
with v as (
select      C.REFERENCE as NUM_EDL
             , DECODE(A.STATUT,1,'actif',2,'en cours de modification',3,'en cours de cessation') AS STATUT_CONTRAT
             , A.DATECREATION
             , F.LIBELLE as TYPE_COMPTEUR
             , E.DATERELEVE
             , G.LIBELLE as TYPE_INDEX
			       , G.VALEUR as INDEX_CONSO
             , ROW_NUMBER() OVER (PARTITION BY C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE ORDER BY E.DATERELEVE DESC) AS rn   
from
     VRP_CONTRAT                            A
     left join CONTRAT_ESPACESDELIVRAISON   B  on  B.SOURCE = A.ID
     left join VRP_ESPACEDELIVRAISON        C  on  C.ID = B.DEST
     left join VRS_CONTRAT_RELEVES          D  on  A.ID = D.SOURCE
     left join VRP_RELEVE                   E  on  D.DEST = E.ID
     left join VRS_CONFIGURATIONMATERIELLE  F  on  F.ID = E.PACM_ID
     left join VRS_GRANDEURPHYSIQUE         G  on  G.RELEVE_ID = E.ID
where A.STATUT in (1, 2, 3) 
      and F.DATEFIN is null 
      and F.LIBELLE in ('BT HP/HC Electronique', 'BT HP/HC Electromécanique') 
      and E.NATURERELEVE in (1,6,41)
      and E.STATUTRELEVE in (1, 3)  
      and lower(G.LIBELLE) like '%index nrj active%' 
      and E.DATERELEVE >=TO_DATE('01/01/2018 00:00','dd/mm/yyyy hh24:mi')
      and E.DATERELEVE <=TO_DATE('31/10/2021 00:00','dd/mm/yyyy hh24:mi')
 
and  (A.CODEGRD = 'G-EDF' or A.CODEFOURNISSEUR = 'F-EDF') and mod(A.ETATOBJET,2)=0  
and  (C.CODEGRD = 'G-EDF' or C.CODEFOURNISSEUR = 'F-EDF') and mod(C.ETATOBJET,2)=0
)
SELECT NUM_EDL, 
       STATUT_CONTRAT,
	   DATECREATION,
	   TYPE_COMPTEUR,
	   DATERELEVE,
	   TYPE_INDEX,
	   INDEX_CONSO
FROM v
WHERE rn <= 2;

J'ai mis en pièce jointe, le résultat de la requête ci-dessous et ce que je souhaite obtenir.
Si un client n'a pas 2 dates de relève, je souhaiterais qu'il apparaisse en erreur.

Je suppose que le calcul doit se faire dans le 2nd select mais comme les données sont en lignes et non en colonnes, je bug.
Il faut faire le calcul selon le type d'index et par client.exemple.xlsx

**vanagreg** · 03/11/2021, 15h57

Bonjour,

Oui tu peux faire directement en SQL. Si tu veux également contrôler le nombre de lignes, tu peux utiliser la fonction COUNT dans sa version analytique aussi. Quelque chose comme ça :

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
 
with v as (
select      C.REFERENCE as NUM_EDL
             , DECODE(A.STATUT,1,'actif',2,'en cours de modification',3,'en cours de cessation') AS STATUT_CONTRAT
             , A.DATECREATION
             , F.LIBELLE as TYPE_COMPTEUR
             , E.DATERELEVE
             , G.LIBELLE as TYPE_INDEX
			       , G.VALEUR as INDEX_CONSO
             , ROW_NUMBER() OVER (PARTITION BY C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE ORDER BY E.DATERELEVE DESC) AS rn   
             , COUNT(*) OVER (PARTITION BY C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE) cnt
from
     VRP_CONTRAT                            A
     left join CONTRAT_ESPACESDELIVRAISON   B  on  B.SOURCE = A.ID
     left join VRP_ESPACEDELIVRAISON        C  on  C.ID = B.DEST
     left join VRS_CONTRAT_RELEVES          D  on  A.ID = D.SOURCE
     left join VRP_RELEVE                   E  on  D.DEST = E.ID
     left join VRS_CONFIGURATIONMATERIELLE  F  on  F.ID = E.PACM_ID
     left join VRS_GRANDEURPHYSIQUE         G  on  G.RELEVE_ID = E.ID
where A.STATUT in (1, 2, 3) 
      and F.DATEFIN is null 
      and F.LIBELLE in ('BT HP/HC Electronique', 'BT HP/HC Electromécanique') 
      and E.NATURERELEVE in (1,6,41)
      and E.STATUTRELEVE in (1, 3)  
      and lower(G.LIBELLE) like '%index nrj active%' 
      and E.DATERELEVE >=TO_DATE('01/01/2018 00:00','dd/mm/yyyy hh24:mi')
      and E.DATERELEVE <=TO_DATE('31/10/2021 00:00','dd/mm/yyyy hh24:mi')
 
and  (A.CODEGRD = 'G-EDF' or A.CODEFOURNISSEUR = 'F-EDF') and mod(A.ETATOBJET,2)=0  
and  (C.CODEGRD = 'G-EDF' or C.CODEFOURNISSEUR = 'F-EDF') and mod(C.ETATOBJET,2)=0
),
agg as (SELECT NUM_EDL, 
               STATUT_CONTRAT,
               DATECREATION,
               TYPE_COMPTEUR,
               sum(case when type_index like '%HC%' and cnt > 1 then index_conso 
                        when type_index like '%HC%' and cnt = 1 then -1
                        else 0 
                   end) volume_HC ,
               sum(case when type_index like '%HP%' and cnt > 1 then index_conso 
                        when type_index like '%HP%' and cnt = 1 then -1
                        else 0 
                   end) volume_HP
        FROM v
        WHERE rn <= 2
        GROUP BY NUM_EDL, 
                 STATUT_CONTRAT,
                 DATECREATION,
                 TYPE_COMPTEUR
       )
select num_edl,
       statu_contrat,
       datecreation,
       type_compteur,
       case when volume_HC = -1 then 'erreur' else to_char(volume_HC) end volume_HC,
       case when volume_HP = -1 then 'erreur' else to_char(volume_HP) end volume_HP
from agg;

J'ai ajouté la fonction COUNT après ROW_NUMBER. Je m'en sers dans le bloc "agg" pour savoir si j'ai plus d'une ligne ou non. Par contre, étant donné que tu mélanges le type de données (nombre d'un côté, et "erreur" de l'autre, je force -1 quand CNT=1 pour savoir que c'est un cumul en erreur. C'est pour cela que dans la requête finale il faut faire une conversion.
Autre chose, je ne comprends pas les cumuls dans ton fichier résultat, ils n'ont pas l'air d'être la somme des 2 dates?

Autre remarques importante, tu fais des LEFT JOIN, mais tu références des colonnes des tables jointes en externe dans la clause WHERE, donc ça annule la jointure externe. Il faut que tu places ces conditions dans les clause ON si tu veux conserver les jointures externes.

**Waldar** · 03/11/2021, 17h47

Un peu différent :

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
with v as
(
select C.REFERENCE as NUM_EDL
     , case A.STATUT
         when 1 then 'actif'
         when 2 then 'en cours de modification'
         when 3 then 'en cours de cessation'
       end as STATUT_CONTRAT
     , A.DATECREATION
     , F.LIBELLE as TYPE_COMPTEUR
     , substr(G.LIBELLE, -2) as TYPE_INDEX
     , row_number() over (partition by C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE order by E.DATERELEVE desc) as rn
     , G.VALEUR - lead(G.VALEUR) over (partition by C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE order by E.DATERELEVE desc) as VOLUME
  from VRP_CONTRAT                  A
  join CONTRAT_ESPACESDELIVRAISON   B  on B.SOURCE    = A.ID
  join VRP_ESPACEDELIVRAISON        C  on C.ID        = B.DEST
  join VRS_CONTRAT_RELEVES          D  on D.SOURCE    = A.ID
  join VRP_RELEVE                   E  on E.ID        = D.DEST
  join VRS_CONFIGURATIONMATERIELLE  F  on F.ID        = E.PACM_ID
  join VRS_GRANDEURPHYSIQUE         G  on G.RELEVE_ID = E.ID
 where A.STATUT           in (1, 2, 3)
   and F.DATEFIN          is null
   and F.LIBELLE          in ('BT HP/HC Electronique', 'BT HP/HC Electromécanique')
   and E.NATURERELEVE     in (1,6,41)
   and E.STATUTRELEVE     in (1, 3)
   and lower(G.LIBELLE) like '%index nrj active%'
   and E.DATERELEVE       >= date '2018-01-01'
   and E.DATERELEVE       <= date '2021-10-31'
   and (A.CODEGRD          = 'G-EDF'
    or  A.CODEFOURNISSEUR  = 'F-EDF')
   and mod(A.ETATOBJET, 2) = 0
   and (C.CODEGRD          = 'G-EDF'
    or  C.CODEFOURNISSEUR  = 'F-EDF')
   and mod(C.ETATOBJET, 2) = 0
)
  ,  cte_rn1 as
(
select NUM_EDL, STATUT_CONTRAT, DATECREATION, TYPE_COMPTEUR, TYPE_INDEX, VOLUME
  from V
 where rn = 1
)
select *
  from cte_rn1
 pivot (max(VOLUME) for TYPE_INDEX in ('HC', 'HP'));

**TsunadeSama** · 03/11/2021, 20h00

Autre chose, je ne comprends pas les cumuls dans ton fichier résultat, ils n'ont pas l'air d'être la somme des 2 dates?

Effectivement, ce n'est pas une somme mais une soustraction entre les index de la date la plus récente et de l'avant dernière.
Le 1er programme fonctionne très bien mais il fait une somme.

Le 2nd programme correspond exactement à ce que je recherche. Il y a cependant une petite erreur sur la dernière ligne de code. FROM au lieu de FOR.
Je remets donc le programme exacte pour ce que ca aiderait.

Merci beaucoup pour ton aide.

Code sql :

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
with v as
(
select C.REFERENCE as NUM_EDL
     , case A.STATUT
         when 1 then 'actif'
         when 2 then 'en cours de modification'
         when 3 then 'en cours de cessation'
       end as STATUT_CONTRAT
     , A.DATECREATION
     , F.LIBELLE as TYPE_COMPTEUR
     , substr(G.LIBELLE, -2) as TYPE_INDEX
     , row_number() over (partition by C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE order by E.DATERELEVE desc) as rn
     , G.VALEUR - lead(G.VALEUR) over (partition by C.REFERENCE, A.STATUT, A.DATECREATION, F.LIBELLE, G.LIBELLE order by E.DATERELEVE desc) as VOLUME
  from VRP_CONTRAT                  A
  join CONTRAT_ESPACESDELIVRAISON   B  on B.SOURCE    = A.ID
  join VRP_ESPACEDELIVRAISON        C  on C.ID        = B.DEST
  join VRS_CONTRAT_RELEVES          D  on D.SOURCE    = A.ID
  join VRP_RELEVE                   E  on E.ID        = D.DEST
  join VRS_CONFIGURATIONMATERIELLE  F  on F.ID        = E.PACM_ID
  join VRS_GRANDEURPHYSIQUE         G  on G.RELEVE_ID = E.ID
 where A.STATUT           in (1, 2, 3)
   and F.DATEFIN          is null
   and F.LIBELLE          in ('BT HP/HC Electronique', 'BT HP/HC Electromécanique')
   and E.NATURERELEVE     in (1,6,41)
   and E.STATUTRELEVE     in (1, 3)
   and lower(G.LIBELLE) like '%index nrj active%'
   and E.DATERELEVE       >= date '2018-01-01'
   and E.DATERELEVE       <= date '2021-10-31'
   and (A.CODEGRD          = 'G-EDF'
    or  A.CODEFOURNISSEUR  = 'F-EDF')
   and mod(A.ETATOBJET, 2) = 0
   and (C.CODEGRD          = 'G-EDF'
    or  C.CODEFOURNISSEUR  = 'F-EDF')
   and mod(C.ETATOBJET, 2) = 0
)
  ,  cte_rn1 as
(
select NUM_EDL, STATUT_CONTRAT, DATECREATION, TYPE_COMPTEUR, TYPE_INDEX, VOLUME
  from V
 where rn = 1
)
select *
  from cte_rn1
 pivot (max(VOLUME) for TYPE_INDEX in ('HC', 'HP'));

**vanagreg** · 04/11/2021, 09h22

Et l'affichage du libellé "erreur"??

**Waldar** · 04/11/2021, 10h58

Ça affichera un null s'il n'y a qu'une valeur, ça me paraît acceptable.

**McM** · 04/11/2021, 11h48

C'est mieux que de tester ou forcer une valeur -1 qui peut arriver.
Pour info, j'ai déjà eu des cas personnellement d'un relevé de compteur inférieur au précédent (dû à une erreur de l'agent des eaux, ou pour EDF dû à un changement de compteur)

La solution pour utiliser une valeur significative, est d'utiliser une valeur jamais produite . Exemple si les champs relevés n'ont pas de décimales, il suffit de mettre une valeur avec décimale (-3.14) par exemple et la tester.

Faire un calcul par lignes par client selon le type

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager