calcul par fonction analytique

**aurelie83** · 24/09/2007, 17h50

Je n'arrive pas a m'en sortir.
J'ai une requete qui me sort les contrats de chaque salarie ainsi que le temps de travail.
Mon souci est que lorsqu'un salarie prolonge un CDD, je dois avoir qu'une seule ligne pour ce salarie avec la date de début , la date de fin et la durée totale.
Si par contre, le CDD est repris plus tard, ca me fait 2 CDD pour ce salarie...

CEM_LIB CTR_LIB SAL_MATR TEMPS_ID_DEB TEMPS_ID_FIN DUREE TEM_TAUX DATE_TEMPS_CONTRAT
Complet Durée déterminée 130207 11/07/2005 31/12/2005 173 0.0 0
Complet Durée déterminée 130207 01/01/2006 31/03/2006 89 0.0 0
Complet Durée déterminée 130207 01/04/2006 31/12/2006 274 0.0 0
Complet Durée déterminée 130207 01/01/2007 31/03/2007 89 0.0 0

Voici ma requete :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT E.CEM_LIB
,CTR_LIB
,H.SAL_MATR
, TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2)as TEMPS_ID_DEB
, TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2)as TEMPS_ID_FIN
 
, DECODE (H.CON_AU,'0','',(H.CON_AU) - (H.CON_DU)) as DUREE
, HP.TEM_TAUX 
,HP.TEM_DATE 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

Merci d'avance de votre aide...

**Yorglaa** · 24/09/2007, 23h18

Salut,
je voudrais être sûr de comprendre :
ton soucis est qu'un salarié peut avoir de mutliples CDD, parfois bout à bout et parfois avec des "trous" entre deux ... c'est bien ça ?

donc tu souhaites pouvoir calculer les différentes durées de travail en continuités, que chaque continuité soit composée de 1 à n CDD, pour autant que ces CDD ne présentent pas de "trous" dans leurs dates...

exemple, pour 1 employé :
CDD1 = du 01.01.2007 au 28.02.2007
CDD2 = du 01.03.2007 au 30.04.2007
CDD3 = du 01.06.2007 au 30.06.2007

Résultat :
Durée 1 = 4 mois (consécutifs) du 01.01.2007 au 30.04.2007 (CDD 1+2)
Durée 2 = 1 mois du 01.06.2007 au 30.06.2007 (CDD 3)

j'ai bon là ?

tu bosses avec quelle version de Oracle ?

**laurentschneider** · 25/09/2007, 10h15

je peux imaginer quelque chose avec lag et lead du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
alter session set nls_date_format='DD/MM/YYYY';
create table t(CEM_LIB varchar2(10),CTR_LIB varchar2(20),SAL_MATR number,TEMPS_ID_DEB date,TEMPS_ID_FIN date, TEM_TAUX number,DATE_TEMPS_CONTRAT number);
insert into t values ('Complet','Durée déterminée',130207,'11/07/2005','31/12/2005',0.0,0);
insert into t values ('Complet','Durée déterminée',130207,'01/01/2006','31/03/2006',0.0,0);
insert into t values ('Complet','Durée déterminée',130207,'01/04/2006','31/12/2006',0.0,0);
insert into t values ('Complet','Durée déterminée',130207,'01/01/2007','31/03/2007',0.0,0);
insert into t values ('Complet','Durée déterminée',130207,'01/01/2008','31/03/2008',0.0,0);
insert into t values ('Complet','Durée déterminée',130208,'01/01/2005','31/03/2009',0.0,0);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
select  distinct
  SAL_MATR,
  last_value(DEB ignore nulls) over (order by TEMPS_ID_DEB) DEB,
  last_value(FIN ignore nulls) over (order by TEMPS_ID_FIN desc) FIN,
  last_value(FIN ignore nulls) over (order by TEMPS_ID_FIN desc) -
  last_value(DEB ignore nulls) over (order by TEMPS_ID_DEB) + 1 DUREE
from (
select
  SAL_MATR,
  TEMPS_ID_DEB,
  TEMPS_ID_FIN,
  decode(
    lag(TEMPS_ID_FIN) over (partition by sal_matr order by TEMPS_ID_FIN)+1,
    TEMPS_ID_DEB,to_date(null),
    TEMPS_ID_DEB) DEB,
  decode(
    lead(TEMPS_ID_DEB) over (partition by sal_matr order by TEMPS_ID_FIN)-1,
    TEMPS_ID_FIN,to_date(null),
    TEMPS_ID_FIN) FIN
from t
)
order by 1,2,3
;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
  SAL_MATR DEB        FIN             DUREE
---------- ---------- ---------- ----------
    130207 11/07/2005 31/03/2007        629
    130207 01/01/2008 31/03/2008         91
    130208 01/01/2005 31/03/2009       1551

**aurelie83** · 26/09/2007, 09h09

Envoyé par Yorglaa

Salut,
exemple, pour 1 employé :
CDD1 = du 01.01.2007 au 28.02.2007
CDD2 = du 01.03.2007 au 30.04.2007
CDD3 = du 01.06.2007 au 30.06.2007

Résultat :
Durée 1 = 4 mois (consécutifs) du 01.01.2007 au 30.04.2007 (CDD 1+2)
Durée 2 = 1 mois du 01.06.2007 au 30.06.2007 (CDD 3)

tu bosses avec quelle version de Oracle ?

Oui c'est bien ca, tu as tt compris.

Je bosse avec la version 9i d'Oracle.

**aurelie83** · 26/09/2007, 09h39

J'ai testé la requete avec mes champs en plus.
Ca donne ca:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT  DISTINCT
  H.SAL_MATR,
  last_value(DEB IGNORE nulls) over (ORDER BY TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2)) DEB,
  last_value(FIN IGNORE nulls) over (ORDER BY TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2) DESC) FIN,
  last_value(FIN IGNORE nulls) over (ORDER BY TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2) DESC) -
  last_value(DEB IGNORE nulls) over (ORDER BY TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2)) + 1 DUREE
, HP.TEM_TAUX 
,HP.TEM_DATE as DATE_TEMPS_CONTRAT
FROM (
SELECT
  TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2) as TEMPS_ID_DEB,
 TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2)as TEMPS_ID_FIN,
  decode(
    lag(TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2)) over (partition BY sal_matr ORDER BY TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2))+1,
    TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2),to_date(NULL),
    TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2)) DEB,
  decode(
    lead(TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_DU + 2)) over (partition BY sal_matr ORDER BY TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2))-1,
    TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2),to_date(NULL),
    TO_CHAR(TO_DATE('30.12.1899', 'DD/MM/YYYY') + H.CON_AU + 2)) FIN
, HP.TEM_TAUX 
,HP.TEM_DATE 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
)
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
 
ORDER BY 1,2,3

Mais par contre, oracle me renvoie:

ORA-00933: la commande SQL ne se termine pas correctement

Je n'ai aucune idée de mon erreur...

**laurentschneider** · 26/09/2007, 09h52

hélas IGNORE NULLS c'est du 10g. Un peu de patience pendant que j'écris qqch pour 9i

**laurentschneider** · 26/09/2007, 10h16

on peut la tenter avec un max

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
select * from (
SELECT
SAL_MATR,
max(DEB) over (partition by sal_matr ORDER BY TEMPS_ID_DEB) DEB,
max(FIN) over (partition by sal_matr ORDER BY TEMPS_ID_DEB ) FIN
FROM (
SELECT
SAL_MATR,
TEMPS_ID_DEB,
decode(
lag(TEMPS_ID_FIN) over (partition BY sal_matr ORDER BY TEMPS_ID_FIN)+1,
TEMPS_ID_DEB,to_date(NULL),
TEMPS_ID_DEB) DEB,
decode(
lead(TEMPS_ID_DEB) over (partition BY sal_matr ORDER BY TEMPS_ID_FIN)-1,
TEMPS_ID_FIN,to_date(NULL),
TEMPS_ID_FIN) FIN
FROM t
)
)
where deb is not null and fin is not null
ORDER BY 1,2,3;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
  SAL_MATR DEB                 FIN
---------- ------------------- -------------------
    130207 11.07.2005 00:00:00 31.03.2007 00:00:00
    130207 01.01.2008 00:00:00 31.03.2008 00:00:00
    130208 01.01.2005 00:00:00 31.03.2009 00:00:00

calcul par fonction analytique

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager