1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
|
SELECT mat, MIN(date_deb), MAX(date_fin),CEM_LIB,CTR_LIB,SAL_NOSS, TAUX_TEMPS_PARTIEL,DATE_TEMPS_CONTRAT
FROM (
SELECT mat, SUM(calc) OVER( PARTITION BY mat ORDER BY date_deb) AS sum_calc,
date_deb, date_fin,CEM_LIB,CTR_LIB,SAL_NOSS, TAUX_TEMPS_PARTIEL, DATE_TEMPS_CONTRAT
FROM (
SELECT H.SAL_MATR mat, DECODE(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2, lag(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2 + 1) OVER (PARTITION BY H.sal_matr ORDER BY TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2), 0, 1) AS calc,
TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2 date_deb, TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2 date_fin,E.CEM_LIB,CTR_LIB,SAL_NOSS
,DECODE (E.CEM_ID,'P', HP.TEM_TAUX) as TAUX_TEMPS_PARTIEL
,DECODE (E.CEM_ID,'P', TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + HP.TEM_DATE + 2),TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2)) as DATE_TEMPS_CONTRAT
FROM SALARIE S LEFT JOIN H_CONTRAT H ON H.SAL_MATR=S.SAL_MATR
LEFT JOIN CCMX.H_TEMPART HP ON HP.SAL_MATR=S.SAL_MATR
LEFT JOIN AR_CONTRAT A ON A.CTR_ID=H.CTR_ID
LEFT JOIN AR_CEMPLOI E ON E.CEM_ID=S.CEM_ID
)
)
GROUP BY mat, sum_calc,CEM_LIB,CTR_LIB,SAL_NOSS, TAUX_TEMPS_PARTIEL,DATE_TEMPS_CONTRAT |
Partager