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
| SELECT DISTINCT
SALARIE.NUM_SECU,
REMU_THEORIQUE.REMU_mensuelle,
REMU_THEORIQUE.OBAN,
REMU_THEORIQUE.REMU_ANNUELLE,
REMU_THEORIQUE.DATE_AUGMENTATION
, REMU_THEORIQUE.PCT_AUGMENT_AUGMENT
FROM
SALARIE,
(
SELECT distinct nom, prenom, sal_id, temps_id, date_augmentation,remu_annuelle, remu_mensuelle, num_secu, matricule , OBAN
,ABS((lag(remu_mensuelle, 1) OVER ( PARTITION BY nom, prenom, sal_id , num_secu ORDER BY temps_id) - remu_mensuelle) / (lag(remu_mensuelle, 1) OVER ( PARTITION BY nom, prenom, sal_id , num_secu ORDER BY temps_id))*100) AS pct_augment
,ABS((lag(remu_mensuelle, 1) OVER ( PARTITION BY nom, prenom, sal_id , num_secu ORDER BY date_augmentation ) - remu_mensuelle) / (lag(remu_mensuelle, 1) OVER ( PARTITION BY nom, prenom, sal_id , num_secu ORDER BY date_augmentation))*100) AS pct_augment_augment
FROM(
--Année 2001
(SELECT distinct nom, prenom, sal_id, temps_id,date_augmentation, MAX(remu_annuelle) as remu_annuelle, MAX(remu_mensuelle) as remu_mensuelle, num_secu, matricule , MAX(OBAN) as OBAN
FROM(
SELECT distinct s.nom, s.prenom, sal_id, temps_id, num_secu, matricule, date_augmentation,
MAX(GREATEST( ... )) AS remu_annuelle
,MAX(GREATEST( ...)) AS remu_mensuelle
, NVL(OBAN, 0) as OBAN
FROM FRH, SALARIE s
WHERE ..
GROUP BY ..
ORDER BY ..
)group by ..)
union
--Année 2002
(SELECT distinct nom, prenom, sal_id, temps_id,date_augmentation, MAX(remu_annuelle) as remu_annuelle, MAX(remu_mensuelle) as remu_mensuelle, num_secu, matricule , MAX(OBAN) as OBAN
FROM(
SELECT distinct s.nom, s.prenom, sal_id, temps_id, num_secu, matricule, date_augmentation,
MAX(GREATEST( ... )) AS remu_annuelle
,MAX(GREATEST( ... )) AS remu_mensuelle
, NVL(OBAN, 0) as OBAN
FROM FRH, SALARIE s
WHERE ...
GROUP BY ...
ORDER BY ...
)
group by nom, prenom, sal_id, temps_id,date_augmentation,num_secu, matricule) |
Partager