J'ai trouvé. Merci encore à tous pour votre aide.
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
SELECT hophabs.dat,
round(sum ( CASE  WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') then 1 else 0 end),0) as SPPJ,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') AND PLPEMCRI.MATRI is not null then 1 else 0 end),0) as NBPLG3,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  AND PLG2.MATRI is not null then 1 else 0 end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') AND PLPEMCRI.MATRI is not null then 1else 0 end),0)as NBPLG2,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  AND PLG1.MATRI is not null then 1 else 0 end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP')  AND PLG2.MATRI is not null then 1 else 0 end),0) as NBPLG1,
PresentsJ.ListePresentJ,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA',  'WBCGNPCETC','WBCG12NP',  'WBCG24NP') then 1 else 0 end),0) as SPPN,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG3N.MATRI is not null then 1 else 0end),0) as NBPLG3N,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG2N.MATRI is not null then 1 else 0 end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG3N.MATRI is not null then 1 else 0 end),0)as NBPLG2N,
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG1N.MATRI is not null then 1else 0end),0)-
round(sum (CASE WHEN HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA','WBCG12NP',  'WBCG24NP') AND PLG2N.MATRI is not null then 1 else 0 end),0) as NBPLG1N,
Presents.ListePresent
from HOPHABS
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) 
PLPEMCRI on PLPEMCRI.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2
 on PLG2.MATRI = HOPHABS.MATRI
 
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1
 on PLG1.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG3') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG3N
 on PLG3N.MATRI = HOPHABS.MATRI
 
LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG2') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG2N
 on PLG2N.MATRI = HOPHABS.MATRI
 
 LEFT JOIN 
( SELECT DISTINCT PLPEMCRI.MATRI
FROM PLPEMCRI
WHERE PLPEMCRI.CODECRIT in ('PLG1') AND PLPEMCRI.DATFIN = '31/12/2099'
) PLG1N
 on PLG1N.MATRI = HOPHABS.MATRI
 left join
      (
        select LISTAGG(hopempl.nompre, '; ') WITHIN GROUP (ORDER BY hophabs.MATRI) as ListePresent,hophabs.DAT
        from hophabs, hopempl, plpEMCRI
 
        WHERE HOPHABS.MATRI = HOPEMPL.MATRI and PLPEMCRI.MATRI = HOPHABS.MATRI and HOPHABS.MOTIF in ('WBCG12NPCA',  'WBCG24NPCA',  'WBCG12NP',  'WBCG24NP') AND PLPEMCRI.CODECRIT ='PLG1' AND PLPEMCRI.DATFIN = '31/12/2099'
 
        group by hophabs.DAT
 
      ) Presents
      on hophabs.DAT = Presents.DAT
 left join
      (
        select LISTAGG(hopempl.nompre, '; ') WITHIN GROUP (ORDER BY hophabs.MATRI) as ListePresentJ,hophabs.DAT
        from hophabs, hopempl, plpEMCRI
 
        WHERE HOPHABS.MATRI = HOPEMPL.MATRI and PLPEMCRI.MATRI = HOPHABS.MATRI and HOPHABS.MOTIF in ('WBCG12JPCA',  'WBCG24JPCA',  'WBCGJPCETC','WBCG12JP',  'WBCG24JP') AND PLPEMCRI.CODECRIT ='PLG1' AND PLPEMCRI.DATFIN = '31/12/2099'
 
        group by hophabs.DAT
 
      ) PresentsJ
      on hophabs.DAT = PresentsJ.DAT
WHERE  extract ( year from hophabs.dat) = '2019' and EXTRACT(MONTh from HOPHABS.DAT) = '10' 
 
GROUP BY HOPHABS.DAT,Presents.ListePresent,PresentsJ.ListePresentJ
ORDER BY HOPHABS.DAT