Discussion :

[rvaysse]

Bonjour à tous

J'ai une requete de ce 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
select TO_CHAR(count(case When f.analysis_type in  ('BI','SNES','BULL_TR') Then c.name End)) AS NbDemande,
  f.analysis_type as Bulletin,
  DECODE(c.x_l_type_client,'C','PRIVE','S','SOC','Q','SEV-MAGNERAUD','G','GEVES') as Client
    from (select sum(d.X_N_DUREE) as duree,a.name as project
          from project a,DEM05_PRESTATIONS b,DEM01_DETAIL_PREST c,TEST_LIST d
          where a.name = b.Project
          AND a.name = c.Project
          AND b.D05_A_VRCI_NO = c.D01_A_VRCI_NO
          AND c.D01_A_VRLISTPLAN_ANA = d.name
          GROUP BY a.name) a
          ,
        (select max(b.D05_D_RENDU_SNES) as dateSNES,a.name as project
          from project a,DEM05_PRESTATIONS b,DEM01_DETAIL_PREST c
          where a.name = b.Project
          AND a.name = c.Project
          AND b.D05_A_VRCI_NO = c.D01_A_VRCI_NO
          GROUP BY b.D05_D_RENDU_SNES,a.name) b,
          project c,
          DEM01_DETAIL_PREST d,
          TEST_LIST_ENTRY e,
          ANALYSIS f
    where a.project = b.project
    AND a.project = c.name
    AND b.project = c.name
    AND c.date_created <= b.dateSNES-a.duree-7
    AND c.name = d.project
    AND d.D01_A_VRLISTPLAN_ANA = e.name
    AND e.analysis = f.name
    AND f.analysis_type in ('BI','SNES','BULL_TR')
    AND (c.x_l_type_client is not null AND c.x_l_type_client != 'T')
    group by f.analysis_type,c.x_l_type_client
    order by f.analysis_type

Le résultat est le suivant :
358------BI--------PRIVE
4206-----BI--------SOC
4--------BULL_TR--SEV-MAGNERAUD
178------BULL_TR--PRIVE
1101-----BULL_TR--GEVES
4068-----SNES-----SEV-MAGNERAUD
4550-----SNES-----PRIVE
57939----SNES-----SOC

J'aimerai obtenir un résultat comme suis :
Nb------Bulletin------Client---------------somme/Bulletin
358-----BI-----------PRIVE---------------4564
4206----BI-----------SOC----------------4564
4------ BULL_TR-----SEV-MAGNERAUD----1283
178-----BULL_TR-----PRIVE---------------1283
1101----BULL_TR-----GEVES--------------1283
4068----SNES--------SEV-MAGNERAUD----66557
4550----SNES--------PRIVE---------------66557
57939---SNES--------SOC----------------66557

En gros, j'aimerai obtenir une somme pour chaque type de bulletin.
Est ce possible, si oui comment?

Merci d'avance

ps : je sais je sais, le tuning de ma requete est à revoir...