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
| SELECT *
FROM
(
SELECT /*t.DAT as DATCDG1,*/Extract(YEAR FROM t.dat) AS ANNEECDG1,EXTRACT(MONTH FROM t.dat) as MOISCDG1,extract(day from t.dat) as day,
/*hophjoun.jour AS JOUR1,*/e.IEISOCINTE as SITE1,
CASE WHEN t.PROFIL in ('A1','A2') then e.NOMPRE ||' A'
WHEN t.PROFIL in ('B1','B2') then e.NOMPRE || ' B'
WHEN t.PROFIL in ('C1','C2') then e.NOMPRE || ' C'
else ' '
end as TYPO
/*CASE WHEN HOPHJOUN.PROFIL in ('SPOCDG12N','WBCG12NPCG','GTEG12NP','WBCG12NPCG') then HOPEMPL.NOMPRE end as G12N,
CASE WHEN HOPHJOUN.PROFIL in ('SPOCDG24','WBCG24PCG','WBEG24PCG','GTEG24P') then HOPEMPL.NOMPRE end as G24*/
FROM t , e, p
WHERE e.MATRI = t.MATRI
AND p.MATRI = e.MATRI
AND p.MATRI = t.MATRI
AND p.DAT = t.DAT
AND t.PROFIL in ('A1','A2','B1', 'B2','C1','C2')
AND EXTRACT(YEAR FROM t.DAT) >= '2023'
ORDER BY t.DAT
)
PIVOT (
LISTAGG(TYPO,' ') WITHIN GROUP(ORDER BY TYPO )
for day in (1 as J1, 2 as J2, 3 as J3, 4 as J4, 5 as J5, 6 as J6, 7 as J7, 8 as J8, 9 as J9, 10 as J10, 11 as J11, 12 as J12, 13 as J13, 14 as J14, 15 as J15, 16 as J16, 17 as J17, 18 as J18, 19 as J19, 20 as J20, 21 as J21,
22 as J22, 23 as J23, 24 as J24, 25 as J25, 26 as J26, 27 as J27, 28 as J28, 29 as J29, 30 as J30, 31 as J31)
)
order by ANNEECDG1,MOISCDG1; |
