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 |
Partager