Calcul avec jointure bases

Version imprimable

06/09/2007, 17h27
aurelie83

Calcul avec jointure bases
Bonjour,
Mon objectif: compter les jours ouvrés entre 2 dates.
J'ai une table TEMPS qui contient des dates et qui marque à '1' les jours fériés, samedi et dimanche.

TEMPS_ID FERIE
01/11/2005 1
02/11/2005 0
03/11/2005 0
04/11/2005 0
05/11/2005 0
06/11/2005 0
07/11/2005 0
08/11/2005 0
09/11/2005 0
10/11/2005 0
11/11/2005 1

J'ai une autre table des absences dans lequel j'ai une date début et fin.

ABSENCE_ID TEMPS_ID TEMPS_ID_FIN
3 01/11/2005 11/11/2005

Quand je fais un test seulement sur la table TEMPS du genre :
Code:

1 2 3 4 5 SELECT COUNT(TEMPS_ID) FROM DRH_TEMPS WHERE FERIE IS NULL AND TEMPS_ID BETWEEN '01/11/2005' AND '11/11/2005'
ca me renvoit bien : 7.

Par contre dès que j'essaye de faire la jointure avec la table des absences...ca ne fonctionne plus...
Une idée?
Merci
06/09/2007, 17h31
Invité

Citation:

Envoyé par steinia

Par contre dès que j'essaye de faire la jointure avec la table des absences...ca ne fonctionne plus...

C'est à dire ?
On peut voir la tête de ta jointure ?

Du genre :

Code:

1
2
3
4
5
6
SELECT 
COUNT(T.TEMPS_ID)
FROM DRH_TEMPS T, FRH F
WHERE FERIE IS NULL 
AND F.ABSENCE_ID=3
AND F.TEMPS_ID BETWEEN F.TEMPS_ID AND TEMPS_ID_FIN

tu dois vouloir faire quelque chose du style :
Code:

1 2 3 4 5 6 SELECT a.absence_id, COUNT(t.temp_id) FROM temps t, absence a WHERE a.absence_id = 3 AND t.temp_id BETWEEN a.temps_id AND a.temps_id_fin AND (t.ferie IS NULL OR t.ferie = 0)
ce qui au vu de ton modèle de données permet de savoir à combien de jours ouvrés correspond l'absence

06/09/2007, 17h49
Loyd1974
Citation:
Envoyé par steinia

Du genre :

Code:

1 2 3 4 5 6 SELECT COUNT(T.TEMPS_ID) FROM DRH_TEMPS T, FRH F WHERE FERIE IS NULL AND F.ABSENCE_ID=3 AND F.TEMPS_ID BETWEEN F.TEMPS_ID AND TEMPS_ID_FIN
ok, tu as répondu entre temps. Juste un truc, entre l'exemple que tu nous donne et le code, tu es sûr que FERIE est NULL et qu'il ne faut pas faire plutôt un NVL(FERIE,0) = 0 ?

Non ca me renvoit tjs 0....pour cette requete.

Un peu plus d'explication:

Lorsque je fais cette requete:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT SALARIE_ID ,FRH.TEMPS_ID , COUNT(T.TEMPS_ID) as DUREE FROM FRH, SALARIE, DRH_TEMPS T WHERE EVENEMENT_ID='9' AND FRH.ABSENCE_ID='3' AND FRH.TEMPS_ID = T.TEMPS_ID AND FRH.SALARIE_ID=SALARIE.SAL_ID AND MATRICULE='190674' AND NVL(FERIE,0)=0 AND T.TEMPS_ID BETWEEN FRH.TEMPS_ID AND FRH.TEMPS_ID_FIN GROUP BY FRH.TEMPS_ID , SALARIE_ID
J'obtiens :

Citation:

SALARIE_ID TEMPS_ID DUREE
1948 2005/01/18 00:00:00.000 1
1948 2005/02/14 00:00:00.000 1
1948 2005/08/30 00:00:00.000 1

ALors que je devrais obtenir:

Citation:

TEMPS_ID TEMPS_ID_FIN DUREE
2005/01/18 00:00:00.000 2005/01/29 00:00:00.000 9
2005/01/30 00:00:00.000 2005/02/13 00:00:00.000 10
2005/02/14 00:00:00.000 2005/02/26 00:00:00.000 10
2005/02/27 00:00:00.000 2005/03/12 00:00:00.000 10
2005/08/30 00:00:00.000 2005/09/18 00:00:00.000 14

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
create table temps as (
select date '2005-11-01' temps_id, 1 ferie from dual union all
select date '2005-11-02', 0 from dual union all
select date '2005-11-03', 0 from dual union all
select date '2005-11-04', 0 from dual union all
select date '2005-11-05', 0 from dual union all
select date '2005-11-06', 0 from dual union all
select date '2005-11-07', 0 from dual union all
select date '2005-11-08', 0 from dual union all
select date '2005-11-09', 0 from dual union all
select date '2005-11-10', 0 from dual union all
select date '2005-11-11', 1 from dual union all
select date '2005-10-30', 1 from dual union all
select date '2005-11-12', 0 from dual);
create table absence as (select 3 absence_id, date '2005-11-01' 
temps_id, date '2005-11-11' temps_id_fin from dual );

Code:

1
2
3
4
5
6
7
8
9
10
11
12
select
  absence.absence_id,
  absence.temps_id,
  absence.temps_id_fin,
  absence.temps_id_fin-absence.temps_id-sum(temps.ferie)+1 duree
from absence,temps
where temps.temps_id between absence.temps_id and absence.temps_id_fin
group by absence.absence_id, absence.temps_id, absence.temps_id_fin;
 
ABSENCE_ID TEMPS_ID        TEMPS_ID_FIN         DUREE
---------- --------------- --------------- ----------
         3 TUE 01/11 00:00 FRI 11/11 00:00          9

07/09/2007, 10h03
Loyd1974
Tu devrais peut être expliqué plus clairement ce que tu veux obtenir parce que je ne comprends pas quand tu dis que lorsque tu fais :
Code:

1 2 3 4 5 SELECT SALARIE_ID ,FRH.TEMPS_ID , COUNT(T.TEMPS_ID) as DUREE FROM ...
Tu "devrais" obtenir :

Code:

TEMPS_ID TEMPS_ID_FIN DUREE

Les champs ne sont pas les mêmes. Ou bien tu as confondu "devrais" avec "voudrais" et dans ce cas, il faut réécrire la requête
07/09/2007, 13h28
aurelie83

Oui excuse moi, en fait, j'avais fait un fichier excel pour tester mes requetes, c'est pour ca que je n'ai pas les meme champs...
Ce que je veux principalement, c'est la durée en nombre de jours ouvrés. Apres si il ya d'autres champs dans la requete, ce n'est pas un pb, faut juste faire un group by.

Je n'ai pas pu encore tester la requete de Laurentschneider (je ne suis pas au bureau), mais je ne sais pas si c'est moi qui suis fatiguée de cette requete car quand je compte le nombre de jour ouvres entre le 01/11/2005 et le 11/11/2005, je trouve 7 et non 9...
07/09/2007, 13h37
Loyd1974

Citation:

Envoyé par steinia

Je n'ai pas pu encore tester la requete de Laurentschneider (je ne suis pas au bureau), mais je ne sais pas si c'est moi qui suis fatiguée de cette requete car quand je compte le nombre de jour ouvres entre le 01/11/2005 et le 11/11/2005, je trouve 7 et non 9...

La requête de Laurent est bonne, il a juste utilisé les valeurs que tu lui as communiqué, donc, si on regarde dans ton premier post, il y'a entre le 01/11 et le 11/11 seulement 2 jours férié, le 01/11 et le 11/11, tous les autres jours sont à zéro.
Tu as dû oublier de mettre 1 pour samedi et dimanche, d'où ta confusion
J'ai la flemme de rechercher quand tombait samedi et dimanche en novembre 2005, mais si ton jeu de données est correct, tu trouveras bien 7 de ton côté

Alors j'ai testé la requete de Laurent.
Elle fonctionne pour certaines dates, mais pour d'autres je trouve des SUM(jour.ferie) bizarres...
Par ex:

Citation:

TEMPS_ID TEMPS_ID_FIN NB_JOUR_OUVRE NB_JOUR_TOTAL SUM(DRH_TEMPS.FERIE)
2007/08/01 2007/08/31 -23 31 54

En faisant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select frh.temps_id, frh.temps_id_fin , decode(to_date(frh.temps_id_fin)-to_date(frh.temps_id)-sum(drh_temps.ferie)+1,NULL, (frh.temps_id_fin-frh.temps_id) + 1, (to_date(frh.temps_id_fin)-to_date(frh.temps_id)-sum(drh_temps.ferie)+1)) as NB_JOUR_OUVRE ,frh.temps_id_fin-frh.temps_id +1 as nb_jour_total ,sum(drh_temps.ferie) from frh,drh_temps where drh_temps.temps_id between to_date(frh.temps_id) and to_date(frh.temps_id_fin) group by frh.temps_id, frh.temps_id_fin ,frh.temps_id_fin-frh.temps_id +1
Et quand je vais la requete dans ma table drh_temps:
Code:

1 2 3 4 5 SELECT SUM(FERIE) FROM DRH_TEMPS WHERE TEMPS_ID BETWEEN '01/08/2007' AND '31/08/2007'
Je trouve :

Citation:

SUM(FERIE)
9

Vous comprenez qq chose? (car moi je suis completement perdue...) surtout que c'est pour 1/10 de mes lignes que ca me fait ca...

Citation:

Envoyé par steinia

Et quand je fais ... SUM(FERIE) ... WHERE TEMPS_ID BETWEEN '01/08/2007' AND '31/08/2007' ... Je trouve ... 9 ...

difficile de comprendre si c'est juste ou faux et pourquoi ce serait juste ou faux.

Donne nous le contenu de ta table pour 08/2007 et dis-nous le résultat que tu attendais.

Si tu veux éliminer les week-ends, je peux imaginer quelque chose comme
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select a.absence_id, a.temps_id, a.temps_id_fin, a.temps_id_fin-a.temps_id-count(t.temps_id)+1 duree from absence a left join temps t on ( t.temps_id between a.temps_id and a.temps_id_fin and ( t.ferie=1 or t.temps_id - trunc(t.temps_id,'IW')>4)) group by a.absence_id, a.temps_id, a.temps_id_fin; ABSENCE_ID TEMPS_ID TEMPS_ID_F DUREE ---------- ---------- ---------- ---------- 3 2005-11-01 2005-11-11 7

Voila le contenu de ma table drh_temps du 01/08 au 31/08

Citation:

TEMPS_ID ANNEE_ID JOUR_MOIS_ID JOUR_SEMAINE_ID MOIS_ID SEMAINE_ID TRIMESTRE_ID SEMESTRE_ID FERIE
2007/08/01 00:00:00.000 2007 01 3 08 31 3 2
2007/08/02 00:00:00.000 2007 02 4 08 31 3 2
2007/08/03 00:00:00.000 2007 03 5 08 31 3 2
2007/08/04 00:00:00.000 2007 04 6 08 31 3 2 1
2007/08/05 00:00:00.000 2007 05 7 08 31 3 2 1
2007/08/06 00:00:00.000 2007 06 1 08 32 3 2
2007/08/07 00:00:00.000 2007 07 2 08 32 3 2
2007/08/08 00:00:00.000 2007 08 3 08 32 3 2
2007/08/09 00:00:00.000 2007 09 4 08 32 3 2
2007/08/10 00:00:00.000 2007 10 5 08 32 3 2
2007/08/11 00:00:00.000 2007 11 6 08 32 3 2 1
2007/08/12 00:00:00.000 2007 12 7 08 32 3 2 1
2007/08/13 00:00:00.000 2007 13 1 08 33 3 2
2007/08/14 00:00:00.000 2007 14 2 08 33 3 2
2007/08/15 00:00:00.000 2007 15 3 08 33 3 2 1
2007/08/16 00:00:00.000 2007 16 4 08 33 3 2
2007/08/17 00:00:00.000 2007 17 5 08 33 3 2
2007/08/18 00:00:00.000 2007 18 6 08 33 3 2 1
2007/08/19 00:00:00.000 2007 19 7 08 33 3 2 1
2007/08/20 00:00:00.000 2007 20 1 08 34 3 2
2007/08/21 00:00:00.000 2007 21 2 08 34 3 2
2007/08/22 00:00:00.000 2007 22 3 08 34 3 2
2007/08/23 00:00:00.000 2007 23 4 08 34 3 2
2007/08/24 00:00:00.000 2007 24 5 08 34 3 2
2007/08/25 00:00:00.000 2007 25 6 08 34 3 2 1
2007/08/26 00:00:00.000 2007 26 7 08 34 3 2 1
2007/08/27 00:00:00.000 2007 27 1 08 35 3 2
2007/08/28 00:00:00.000 2007 28 2 08 35 3 2
2007/08/29 00:00:00.000 2007 29 3 08 35 3 2
2007/08/30 00:00:00.000 2007 30 4 08 35 3 2
2007/08/31 00:00:00.000 2007 31 5 08 35 3 2

Et quand je fais la requete :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 select f.absence_id, f.temps_id, f.temps_id_fin, f.temps_id_fin-f.temps_id-count(t.temps_id) + 1 duree , count(t.temps_id) as j_ferie , (f.temps_id_fin-f.temps_id) + 1 as temps_total from frh f inner join drh_temps t on ( t.temps_id between f.temps_id and f.temps_id_fin and ( t.ferie=1 or t.temps_id - trunc(t.temps_id,'IW')>4) ) and f.absence_id is not null group by f.absence_id, f.temps_id, f.temps_id_fin
J'obtiens par ex:

Citation:

ABSENCE_ID TEMPS_ID TEMPS_ID_FIN DUREE J_FERIE TEMPS_TOTAL
23 2007/08/01 00:00:00.000 2007/08/31 00:00:00.000 22 9 31
24 2007/08/01 00:00:00.000 2007/08/31 00:00:00.000 -14 45 31

La premiere ligne est bonne mais pas la deuxieme.
C'est vraiment suspect...
Je ne sais pas comment verifier les dates qu'il lit...

bon, déjà il semble que ta table férié contient les week-ends, donc on oublie le trunc(iw)

ensuite, il faut utiliser un outer join, sinon une absence sans jour férié, style du lundi au mardi, ne serait pas comptée.

Enfin, je n'arrive pas à reproduire ton problème.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
select
  a.absence_id, a.temps_id, a.temps_id_fin,
  a.temps_id_fin-a.temps_id-count(t.temps_id)+1 duree
from
  frh a left join drh_temps t
  on (
    t.temps_id between a.temps_id and a.temps_id_fin
    and t.ferie=1)
group by
  a.absence_id, a.temps_id, a.temps_id_fin;
ABSENCE_ID TEMPS_ID   TEMPS_ID_F      DUREE
---------- ---------- ---------- ----------
        24 2007-08-01 2007-08-31         22
        23 2007-08-01 2007-08-31         22

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
select   f.absence_id, f.temps_id, f.temps_id_fin,
f.temps_id_fin-f.temps_id-count(t.temps_id) + 1  duree
 , count(t.temps_id) as j_ferie
, (f.temps_id_fin-f.temps_id) + 1 as temps_total
from
  frh f inner join drh_temps t
  on (
    t.temps_id between f.temps_id and f.temps_id_fin
    and (
      t.ferie=1 or
     t.temps_id - trunc(t.temps_id,'IW')>4)
)
and f.absence_id is not null
group by
  f.absence_id, f.temps_id, f.temps_id_fin
;
ABSENCE_ID TEMPS_ID   TEMPS_ID_F      DUREE    J_FERIE TEMPS_TOTAL
---------- ---------- ---------- ---------- ---------- -----------
        24 2007-08-01 2007-08-31         22          9          31
        23 2007-08-01 2007-08-31         22          9          31

peux-tu me donner les résultat de

Code:

1
2
3
4
 
select * from frh where absence_id=24;
select count(*) from drh_temps 
where temps_id between date '2007-08-01' and date '2007-08-31';

Essaye avec cette requete qui evite une jointure qui pourrait être nulle (cas où datebeb et datefin n'ont pas de jours fériés (lundi au vendredi d'une même semaine))

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT absence_id, temps_id, temps_id_fin, 
	temps_id_fin - temps_id + 1 - j_ferie AS duree,
	j_ferie, 
	temps_id_fin - temps_id + 1 AS temps_total
FROM (
	SELECT   f.absence_id, f.temps_id, f.temps_id_fin,
		NVL((SELECT SUM(t.temps_id) 
		 FROM drh_temps t
		WHERE t.temps_id BETWEEN f.temps_id AND f.temps_id_fin
		),0) AS j_ferie
	FROM frh f
	WHERE f.absence_id IS NOT NULL
)

11/09/2007, 11h41
laurentschneider

dans ta requete,
count(t.temps_id) = -14

ce qui est absolument impossible, count() ne retourne jamais de valeur négative

note: utilise la balise CODE aussi pour tes résultats
11/09/2007, 11h45
McM

Citation:

Envoyé par laurentschneider

dans ta requete,
count(t.temps_id) = -14

ce qui est absolument impossible, count() ne retourne jamais de valeur négative

Faux, c'est

Code:

f.temps_id_fin-f.temps_id-count(t.temps_id) + 1

qui renvoit -14
(Il doit y avoir plusieurs absence_id = 24 avec temps_id = '2007/08/01' et temps_id_fin = '2007/08/31' )

Edit : 5 en fait (45/9)

Citation:

Envoyé par laurentschneider

peux-tu me donner les résultat de
[code]
select * from frh where absence_id=24;

voici un extrait...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
FAIT_ID	SALARIE_ID	ABSENCE_ID	TEMPS_ID	TEMPS_ID_FIN	
2250349	1924	24	2007/08/01 00:00:00.000	2007/08/31 00:00:00.000	
2250463	1986	24	2007/08/01 00:00:00.000	2007/08/31 00:00:00.000	
2250468	1986	24	2007/07/29 00:00:00.000	2007/07/31 00:00:00.000	
2250353	1924	24	2007/07/05 00:00:00.000	2007/07/31 00:00:00.000	
2250162	1718	24	2007/04/01 00:00:00.000	2007/04/11 00:00:00.000	
2250578	2038	24	2007/03/01 00:00:00.000	2007/03/28 00:00:00.000	
2250161	1718	24	2007/03/01 00:00:00.000	2007/03/31 00:00:00.000	
2250577	2038	24	2007/02/01 00:00:00.000	2007/02/28 00:00:00.000	
2250160	1718	24	2007/02/01 00:00:00.000	2007/02/28 00:00:00.000	
2250159	1718	24	2007/01/01 00:00:00.000	2007/01/31 00:00:00.000	
2250575	2038	24	2007/01/01 00:00:00.000	2007/01/31 00:00:00.000	
2250330	1905	24	2006/12/01 00:00:00.000	2006/12/12 00:00:00.000	
2250101	1597	24	2006/12/01 00:00:00.000	2006/12/24 00:00:00.000	
2250579	2038	24	2006/12/01 00:00:00.000	2006/12/31 00:00:00.000

Citation:

Envoyé par laurentschneider

select count(*) from drh_temps
where temps_id between date '2007-08-01' and date '2007-08-31';

Code:

1
2
COUNT(*)
31

bon, c'est pas évident à voir sans balise code (et ça fatigue les yeux).

Bon si absence_id n'est pas unique, alors essayons autre chose...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
select
  a.absence_id,
  min(a.temps_id) temps_id,
  max(a.temps_id_fin) temps_id_fin,
  count(t.temps_id) duree
from
  frh a join drh_temps t
  on (
    t.temps_id between a.temps_id and a.temps_id_fin
    and t.ferie=0)
group by
  a.absence_id;
ABSENCE_ID TEMPS_ID   TEMPS_ID_F      DUREE
---------- ---------- ---------- ----------
        24 2007-08-01 2007-08-31        105
        23 2007-08-01 2007-08-31         21

12/09/2007, 11h30
aurelie83

En fait, ca ne marchait pas car j'avais des lignes avec les memes dates et les meme absences.
J'ai rajouté une autre clé comme ca, maintenant c'est bon.
Merci de votre collaboration!