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
| WITH comptages as (
SELECT t1.FAMILLE as FA,t2.DAT as DAT,t2.MATRI AS MATRI,t3.NOM AS NOM,t4.MOTIF AS MOTIF,
sum(case when t1.FAMILLE = 'PR' then 1 else 0 end) over (partition by t2.matri) cnt_PR,
sum(case when t4.MOTIF = 'A1' then 1 else 0 end) over (partition by t2.matri) cnt_A1,
sum(case when t4.MOTIF = 'A2' then 1 else 0 end) over (partition by t2.matri) cnt_A2,
sum(case when t4.MOTIF = 'A3' then 1 else 0 end) over (partition by t2.matri) cnt_A3
FROM t1,t2, t3,t4
where t1.MOTIF = t4.MOTIF
AND t2.MATRI = t3.MATRI
AND t4.MATRI = t2.MATRI
AND t4.DAT = t2.DAT
AND (t1.FAMILLE in ('PR') OR t4.MOTIF in ('A1','A2','A3'))
AND t2.DAT = to_date('28/05/2020', 'dd/mm/yyyy')
)
SELECT FA,DAT,MATRI,NOM,MOTIF,
case fa when 'PR' then '9'
when 'AST' then '8'
when 'ASTJ' then '8'
when 'ASTN' then '20'
end h1,
case fa when 'PR' then '11.30'
when 'AST' then '32'
when 'ASTJ' then '20'
when 'ASTN' then '32'
end h2,
case fa when 'PR' then '14'
else null
end h3,
case fa when 'PR' then '16.30'
else null
end h4
FROM comptages
WHERE (fa = 'PR' and cnt_A1 + cnt_A2 = 0) OR (motif in ('A1', 'A2', 'A3')) |
Partager