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
|
SELECT distinct nom, prenom, sal_id, temps_id, MAX(remu_annuelle), MAX(remu_mensuelle), num_secu, matricule , MAX(OBAN)
,ABS((lag(remu_mensuelle, 1) OVER ( PARTITION BY nom, prenom, sal_id , num_secu, matricule, OBAN ORDER BY temps_id) - remu_mensuelle) / (lag(remu_mensuelle, 1) OVER ( PARTITION BY nom, prenom, sal_id , num_secu, matricule, OBAN ORDER BY temps_id))*100) AS pct_augment
FROM (
SELECT s.nom, s.prenom, sal_id, temps_id, num_secu, matricule,
MAX(GREATEST(
DECODE(remu_theo_cadre_13,NULL,0,remu_theo_cadre_13*( 13 )),
DECODE(remu_theo_etam_13 ,NULL, 0, remu_theo_etam_13*( 13 )),
DECODE(remu_theo_12,NULL,0, remu_theo_12)
)) AS remu_annuelle
,MAX(GREATEST(
DECODE(remu_theo_cadre_13,NULL,0,remu_theo_cadre_13),
DECODE(remu_theo_etam_13 ,NULL, 0, remu_theo_etam_13),
DECODE(remu_theo_base,NULL,NULL,remu_theo_base)
)) AS remu_mensuelle
, DECODE (OBAN, NULL, 0, OBAN) as OBAN
FROM FRH, SALARIE s
WHERE (remu_theo_12 IS NOT NULL OR remu_theo_cadre_13 IS NOT NULL OR remu_theo_etam_13 IS NOT NULL OR remu_theo_base is not null OR OBAN is not null )
AND frh.salarie_id = s.sal_id
and salarie_id=2039
GROUP BY s.nom, s.prenom, sal_id, temps_id, num_secu, matricule, OBAN
ORDER BY s.nom, s.prenom, sal_id, temps_id, num_secu, matricule, OBAN
)
group by nom, prenom, sal_id, temps_id,num_secu, matricule |
