Agrégation de deux tables

**laserator** · 08/09/2014, 17h55

Bonjour

Je fais face à un petit problème de jointure de 2 tables pour mon projet de gestion du personnel.
Chaque personnel travaille à un taux horaire qui peut varier dans l'année.
Chaque personnel peut être affecté à un poste donné.
Ces 2 tables, marquées par des dates de début et de fin sont indépendantes. Un personnel peut change de poste sans modifier son taux horaire et inversement.

Voici les 2 tables :
Affectation

Poste	Date debut	Date fin
Caisse	10/01/2014	02/02/2014
Stock	03/02/2014	03/03/2014
SAV	04/03/2014	01/09/2014
Livreurs	02/09/2014	NULL

Taux horaire

Taux	Date debut	Date fin
50	01/01/2014	01/04/2014
80	02/04/2014	01/08/2014
90	02/08/2014	NULL

Au final, je veux une table qui contiennent l'historique de ce personnel avec tous les événvements de changement de taux horaire ou d'affectation à un poste.
Le résultat est donc :
HISTORIQUE

Taux	Affectation	Date debut	Date fin
Caisse	50	10/01/2014	02/02/2014
Stock	50	03/02/2014	03/03/2014
SAV	50	04/03/2014	01/04/2014
SAV	80	02/04/2014	01/08/2014
SAV	90	02/08/2014	01/09/2014
Accueil Livreurs	90	02/09/2014	NULL

Idéalement, j'obtiens cette table en une seule requête SQL sans modifier mon modèle de données, ni coder.
Le problème est qu'il y un nombre important de combinaisons et que je ne parviens pas à faire la requête de manière globale.

Auriez-vous une idée ?
Merci
Sylvain

**ojo77** · 08/09/2014, 21h11

Avec le jeu d'essais que vous donnez :

Code :

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

1
2
3
4
5
6
7
8
create table poste as ( 
    select 'Caisse'   Poste, to_date('10/01/2014', 'DD/MM/YYYY') deb, to_date('02/02/2014', 'DD/MM/YYYY') fin from dual
union all
    select 'Stock'    Poste, to_date('03/02/2014', 'DD/MM/YYYY') deb, to_date('03/03/2014', 'DD/MM/YYYY') fin from dual
union all
    select 'SAV'      Poste, to_date('04/03/2014', 'DD/MM/YYYY') deb, to_date('01/09/2014', 'DD/MM/YYYY') fin from dual
union all
    select 'Livreurs' Poste, to_date('02/09/2014', 'DD/MM/YYYY') deb, null                                fin from dual );

Code :

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

1
2
3
4
5
6
create table taux as (
    select 50 Taux, to_date('01/01/2014', 'DD/MM/YYYY') deb, to_date('01/04/2014', 'DD/MM/YYYY') fin from dual
union all
    select 80 Taux, to_date('02/04/2014', 'DD/MM/YYYY') deb, to_date('01/08/2014', 'DD/MM/YYYY') fin from dual
union all
    select 90 Taux, to_date('02/08/2014', 'DD/MM/YYYY') deb, null                                fin from dual ) ;

La requête suivante devrait répondre à vos attentes

Code :

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

1
2
3
4
5
6
7
select p.poste
     , t.taux
     , greatest(t.deb, p.deb) deb
     , least(nvl(t.fin, sysdate ), nvl(p.fin,sysdate) ) fin
from taux t, poste p
where greatest(t.deb, p.deb) <  least(nvl(t.fin, sysdate ), nvl(p.fin,sysdate) )
order by  3 ;

Code :

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

1
2
3
4
5
6
7
8
POSTE          TAUX DEB      FIN
-------- ---------- -------- --------
Caisse           50 10/01/14 02/02/14
Stock            50 03/02/14 03/03/14
SAV              50 04/03/14 01/04/14
SAV              80 02/04/14 01/08/14
SAV              90 02/08/14 01/09/14
Livreurs         90 02/09/14 08/09/14

**laserator** · 09/09/2014, 09h46

Bonjour

Je vous remercie pour votre aide

Je l'ai de mon cote pour un personnel donné (id=6), et voici le résultat.

Affectation. ID_personnel = 6

Poste	Date_debut	Date_fin
Caisse	14/04/14	NULL
Stock	01/02/12	14/11/12
SAV	02/01/11	31/01/12
Livreurs	01/12/10	01/01/11

Taux horaire

Taux	Dtedeb	Dtefin
90	01/09/14	NULL
80	01/01/04	31/08/14

La requete, adaptée est :

Code :

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

1
2
3
4
5
6
7
SELECT t.id_personnel
     ,p.poste
     , t.taux
     , greatest(t.dtedeb, p.date_debut) deb
     , least(nvl(t.dtefin, sysdate ), nvl(p.date_fin,sysdate) ) fin
FROM taux t, poste p
WHERE (greatest(t.dtedeb, p.date_debut) <  least(nvl(t.dtefin, sysdate ), nvl(p.date_fin,sysdate) )) and t.id_personnel = 6;

Le résultat en sortie est :

ID_personne	Poste	Taux	date_debut	date_fin
6	167	90	08/09/14	09/09/14
6	167	90	08/09/14	09/09/14
6	480	90	08/09/14	09/09/14
6	298	90	08/09/14	09/09/14

Les résultats ne correspondent pas du tout (j'ai laisse les codes numériques pour le poste).

J'ai alors mis une jointure.
Les résultats sont plus cohérents, mais pas encore bien juste :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT 
t.id_personnel
,p.poste
     , t.CODREG
     , greatest(t.dtedeb, p.date_debut) deb
     , least(nvl(t.dtefin, sysdate ), nvl(p.date_fin,sysdate) ) fin
FROM  poste p
JOIN taux t ON t.id_personnel= p.id_personnel AND
 (greatest(t.dtedeb, p.date_debut) <  least(nvl(t.dtefin, sysdate ), nvl(p.date_fin,sysdate) )) 
 WHERE t.id_personnel= 6 and t.taux not null
ORDER BY  deb DESC ;

Résultats :

ID_personne	Poste	Taux	date_debut	date_fin
6	Caisse	90	01/09/14	09/09/14
6	128037	90	01/09/14	09/09/14
6	6662	90	01/09/14	09/09/14
6	6661	90	01/09/14	09/09/14
6	6660	90	01/09/14	09/09/14
6	86975	90	01/09/14	09/09/14
6	Caisse	90	14/04/14	31/08/14
6	128037	90	14/04/14	31/08/14
6	116430	90	15/05/13	13/04/14
6	111139	90	15/11/12	14/05/13
6	Stock	80	01/02/12	14/11/12
6	SAV	80	02/01/11	31/01/12
6	86975	80	02/01/11	31/08/14
6	Livreurs	80	01/12/10	01/01/11
6	71886	80	01/01/10	30/11/10
6	70129	80	01/07/09	31/12/09
6	61747	80	01/07/08	30/06/09
6	58312	80	01/10/07	30/06/08
6	56707	80	01/01/07	30/09/07
6	37858	80	01/10/06	31/12/06
6	43640	80	01/11/05	30/09/06
6	37415	80	01/05/05	31/10/05
6	28236	80	01/11/04	01/11/04
6	6661	80	01/01/04	31/08/14
6	46155	80	01/01/04	30/04/05
6	6662	80	01/01/04	31/08/14
6	21403	80	01/01/04	31/10/04
6	6660	80	01/01/04	31/08/14

Moins de résultats, mais les dates ne correspondent pas, à part pour la première ligne.

**ojo77** · 09/09/2014, 12h29

Soyons rigoureux :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE poste AS ( 
    SELECT 6 idp, 'Caisse'   Poste, to_date('10/01/2014', 'DD/MM/YYYY') deb, to_date('02/02/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 6 idp, 'Stock'    Poste, to_date('03/02/2014', 'DD/MM/YYYY') deb, to_date('03/03/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 6 idp, 'SAV'      Poste, to_date('04/03/2014', 'DD/MM/YYYY') deb, to_date('01/09/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 7 idp, 'Caisse'   Poste, to_date('10/01/2014', 'DD/MM/YYYY') deb, to_date('02/02/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 7 idp, 'Stock'    Poste, to_date('03/02/2014', 'DD/MM/YYYY') deb, to_date('03/03/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 7 idp, 'SAV'      Poste, to_date('04/03/2014', 'DD/MM/YYYY') deb, to_date('01/09/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 8 idp, 'Caisse'   Poste, to_date('10/01/2014', 'DD/MM/YYYY') deb, to_date('02/02/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 8 idp, 'Stock'    Poste, to_date('03/02/2014', 'DD/MM/YYYY') deb, to_date('03/03/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 8 idp, 'SAV'      Poste, to_date('04/03/2014', 'DD/MM/YYYY') deb, to_date('01/09/2014', 'DD/MM/YYYY') fin FROM dual union ALL 
    SELECT 6 idp, 'Livreurs' Poste, to_date('02/09/2014', 'DD/MM/YYYY') deb, NULL                                fin FROM dual );

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE taux AS (
    SELECT 6 idp, 50 Taux, to_date('01/01/2014', 'DD/MM/YYYY') deb, to_date('01/04/2014', 'DD/MM/YYYY') fin FROM dual union ALL
    SELECT 6 idp, 80 Taux, to_date('02/04/2014', 'DD/MM/YYYY') deb, to_date('01/08/2014', 'DD/MM/YYYY') fin FROM dual union ALL
    SELECT 6 idp, 90 Taux, to_date('02/08/2014', 'DD/MM/YYYY') deb, NULL                                fin FROM dual union ALL
    SELECT 7 idp, 50 Taux, to_date('01/01/2014', 'DD/MM/YYYY') deb, to_date('01/04/2014', 'DD/MM/YYYY') fin FROM dual union ALL
    SELECT 7 idp, 80 Taux, to_date('02/04/2014', 'DD/MM/YYYY') deb, to_date('01/08/2014', 'DD/MM/YYYY') fin FROM dual union ALL
    SELECT 7 idp, 90 Taux, to_date('02/08/2014', 'DD/MM/YYYY') deb, NULL                                fin FROM dual union ALL
    SELECT 8 idp, 50 Taux, to_date('01/01/2014', 'DD/MM/YYYY') deb, to_date('01/04/2014', 'DD/MM/YYYY') fin FROM dual union ALL
    SELECT 8 idp, 80 Taux, to_date('02/04/2014', 'DD/MM/YYYY') deb, to_date('01/08/2014', 'DD/MM/YYYY') fin FROM dual union ALL
    SELECT 8 idp, 90 Taux, to_date('02/08/2014', 'DD/MM/YYYY') deb, NULL                                fin FROM dual ) ;

Code :

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

1
2
3
4
5
6
7
8
9
SELECT p.poste
     , t.taux
     , greatest(t.deb, p.deb) deb
     , least(nvl(t.fin, sysdate ), nvl(p.fin,sysdate) ) fin
FROM taux t inner join poste p on t.idp=p.idp
WHERE greatest(t.deb, p.deb) <  least(nvl(t.fin, sysdate ), nvl(p.fin,sysdate) )
  AND t.IDP=6
  AND p.IDP=6
ORDER BY  3 ;

Code :

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

1
2
3
4
5
6
7
8
POSTE          TAUX DEB      FIN
-------- ---------- -------- --------
Caisse           50 10/01/14 02/02/14
Stock            50 03/02/14 03/03/14
SAV              50 04/03/14 01/04/14
SAV              80 02/04/14 01/08/14
SAV              90 02/08/14 01/09/14
Livreurs         90 02/09/14 09/09/14

**aieeeuuuuu** · 09/09/2014, 12h59

Bonjour,

Comme ceci ?

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
    P.Poste
    ,T.Taux
    ,CASE WHEN T.Deb < P.Deb THEN P.Deb ELSE COALESCE(T.Deb, P.Deb) END AS Debut
    ,CASE WHEN T.Fin > P.Fin THEN P.Fin ELSE COALESCE(T.Fin, P.Fin) END AS Fin
FROM Poste P
INNER JOIN Taux T
  ON  (T.deb < P.fin OR P.fin IS NULL) 
  AND (T.fin >= P.Deb OR T.fin IS NULL )

**laserator** · 09/09/2014, 14h08

Pour Ojo, je trouve encore des resultats zarbes.

Voici les dumps
Taux :

Code :

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

1
2
3
 
Insert into Taux (Taux,id_personnel,DTEDEB,DTEFIN) values ('80','6',to_date('01/01/04 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/08/14 00:00:00','DD/MM/YY HH24:MI:SS'))
Insert into Taux (Taux,id_personnel,DTEDEB,DTEFIN) values ('90','6',to_date('01/09/14 00:00:00','DD/MM/YY HH24:MI:SS'),null)

Poste :

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
 
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('Caisse',to_date('14/04/14 00:00:00','DD/MM/YY HH24:MI:SS'),null)
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('128037',to_date('14/04/14 00:00:00','DD/MM/YY HH24:MI:SS'),null)
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('116430',to_date('15/05/13 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('13/04/14 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('111139',to_date('15/11/12 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('14/05/13 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('Stock',to_date('01/02/12 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('14/11/12 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('SAV',to_date('02/01/11 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/01/12 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('86975',to_date('02/01/11 00:00:00','DD/MM/YY HH24:MI:SS'),null)
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('Livreurs',to_date('01/12/10 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('01/01/11 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('71886',to_date('01/01/10 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/11/10 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('70129',to_date('01/07/09 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/12/09 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('61747',to_date('01/07/08 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/06/09 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('58312',to_date('01/10/07 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/06/08 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('56707',to_date('01/01/07 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/09/07 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('37858',to_date('01/10/06 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/12/06 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('43640',to_date('01/11/05 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/09/06 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('37415',to_date('01/05/05 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/10/05 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('28236',to_date('01/11/04 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('01/11/04 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('46155',to_date('01/01/04 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/04/05 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('1097',to_date('01/08/03 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/12/03 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('1270',to_date('01/01/01 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/07/03 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('17431',to_date('01/01/00 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/12/00 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('21403',to_date('01/11/96 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/10/04 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('6661',to_date('01/01/93 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/12/99 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('6660',to_date('01/10/92 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('31/12/92 23:59:59','DD/MM/YY HH24:MI:SS'))
Insert into "Poste" (Poste,DATE_DEBUT,DATE_FIN) values ('6662',to_date('01/10/91 00:00:00','DD/MM/YY HH24:MI:SS'),to_date('30/09/92 23:59:59','DD/MM/YY HH24:MI:SS'))

Resultats :

Poste	taux	date debut
Caisse	90	01/09/14	09/09/14
6661	90	01/09/14	09/09/14
6662	90	01/09/14	09/09/14
128037	90	01/09/14	09/09/14
86975	90	01/09/14	09/09/14
6660	90	01/09/14	09/09/14
Caisse	80	14/04/14	31/08/14
128037	80	14/04/14	31/08/14
116430	80	15/05/13	13/04/14
111139	80	15/11/12	14/05/13
Stock	80	01/02/12	14/11/12
SAV	80	02/01/11	31/01/12
86975	80	02/01/11	31/08/14
Livreurs	80	01/12/10	01/01/11
71886	80	01/01/10	30/11/10
70129	80	01/07/09	31/12/09
61747	80	01/07/08	30/06/09
58312	80	01/10/07	30/06/08
56707	80	01/01/07	30/09/07
37858	80	01/10/06	31/12/06
43640	80	01/11/05	30/09/06
37415	80	01/05/05	31/10/05
28236	80	01/11/04	01/11/04
21403	80	01/01/04	31/10/04
46155	80	01/01/04	30/04/05
6662	80	01/01/04	31/08/14
6661	80	01/01/04	31/08/14
6660	80	01/01/04	31/08/14

Entre autres, le taux a 90 ne devrait concerner que la Caisse.
La Caisse est bien marquée à 90 et 80.

On ne devrait avoir que la caisse à 90.

Agrégation de deux tables

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager