pb date de référence sur double comptage

Version imprimable

16/03/2007, 16h16
gg2vig

pb date de référence sur double comptage

Bonjour

ma table activités contient principalement 5 champs

code (pk)
plan
dateprev
datereal
equipement

précisions:
datereal est nulle tant que plan pas réalisé pour équipement
dateprev toujours renseignée

je voudrais compter par un seul select le nombre de réalisés et prévus par plan sur une periode donnée (datedeb-datefin)

groupés par mois (tout equipement confondus)

pour un plan donné

(where plan =:V_PLAN
and ((dateprev between :datedeb and :datefin) or (datereal between :datedeb and :datefin) )

mon problème est de trouver la bonne date de regroupement ci dessous compte tenu des précisions ci dessus

GROUP BY to_char (DATE,'YYYY-MM')

R&sultat attendu:

mois réalisés prévus
2007-01 12 25
2007-02 05 15
2007-03 x y etc

Bonjour ,

Essaies ce code

Code:

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
 
select activites_prev.mois , activites_prev.nb ,activites_real.nb 
from /*Sélectionner tous les mois à partir des dates prevues et réelles avec le code du plan */
(select  plan,to_char(dateprev,'yyyy-mm') mois
		from activites
		where plan =:V_PLAN
			and (dateprev between :datedeb and :datefin)
		union 
		select plan ,to_char(datereal,'yyyy-mm') mois
		from activites
		where plan =:V_PLAN
			and (datereal between :datedeb and :datefin) )activites_princ,
	 ( select code ,to_char(dateprev,'yyyy-mm') mois ,  count(*) nb 
             FROM activites
			where plan =:V_PLAN
			and (dateprev between :datedeb and :datefin)
			group by plan ,to_char(dateprev,'yyyy-mm')
			union /*le cas ou la dateprev n'appartient pas a durée de date*/
			select plan, to_char(datereal,'yyyy-mm') mois ,  0 nb 
            FROM activites
			where plan =:V_PLAN
			and (dateprev < :datedeb or dateprev> :datefin)
			and (datereal between :datedeb and :datefin) ) activites_prev,
			( select plan ,to_char(datereal,'yyyy-mm') mois ,  count(*) nb 
            FROM activites
			where plan =:V_PLAN
			and (datereal between :datedeb and :datefin) and datereal is not null
			group by plan ,to_char(datereal,'yyyy-mm')
			union /* le cas ou la datereal est null ou la date prev appartient  à durée de date*/
			select plan ,to_char(dateprev,'yyyy-mm') mois ,  0 nb 
            FROM activites
			where plan =:V_PLAN and datereal is null  
			and dateprev between :datedeb and :datefin	) activites_real     
where activites_princ.plan =activites_prev.plan 
     and activites_real.plan =activites_prev.plan
	 and activites_real.mois=activites_prev.mois 
     and activites_princ.mois =activites_prev.mois

Un peu plus simple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT mois, SUM(nb_prev) as prévu, SUM(nb_real) as réalisé
FROM (
	SELECT TO_CHAR(dateprev, 'YYYY-MM') AS mois, COUNT(*) AS nb_prev, 0 AS nb_real
	FROM ACTIVITES
	WHERE PLAN = :v_plan
	AND dateprev BETWEEN :datedeb AND :datefin
	GROUP BY TO_CHAR(dateprev, 'YYYY-MM')
	UNION ALL
	SELECT TO_CHAR(datereal, 'YYYY-MM'), 0, COUNT(*) 
	FROM ACTIVITES
	WHERE PLAN = :v_plan
	AND datereal BETWEEN :datedeb AND :datefin)
	GROUP BY TO_CHAR(datereal, 'YYYY-MM')
	) 
GROUP BY mois

19/03/2007, 12h04
gg2vig

merci pour soluces

Pour la 1 ère solution code est une séquence oracle pk de la table activités donc un group by donnerait 1 seule ligne à compter?

La 2 ème solution fonctionne très bien mais maintenant si je veux compter non plus les mois séparement mais en cumulant les prévus et réalisés par mois depuis datedeb jusqu'à datefin est ce réalisable en partant de ce sql ou faut il repenser Complètement le problème?
19/03/2007, 14h07
McM

Citation:

Envoyé par gg2vig

La 2 ème solution fonctionne très bien mais maintenant si je veux compter non plus les mois séparement mais en cumulant les prévus et réalisés par mois depuis datedeb jusqu'à datefin est ce réalisable en partant de ce sql ou faut il repenser Complètement le problème?

On va pas non plus faire le boulot à ta place, faut chercher un peu ;)

Tu as la fonction oracle "+" qui fonctionne très bien :mouarf:
19/03/2007, 19h08
gg2vig

je cherche ...mais plutot coté fonctions statistiques oracle(over ...partition by )

à savoir si en oracle 8i c'est implémenté?

Bonjour ,

Code:

1
2
 
en cumulant les prévus et réalisés par mois depuis datedeb jusqu'à datefin

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT projet.mois, projet.prévu + projet.réalisé
  FROM (SELECT   mois, SUM (nb_prev) AS prévu, SUM (nb_real) AS réalisé
            FROM (SELECT   TO_CHAR (dateprev, 'YYYY-MM') AS mois,
                           COUNT (*) AS nb_prev, 0 AS nb_real
                      FROM activites
                     WHERE PLAN = :v_plan
                       AND dateprev BETWEEN :datedeb AND :datefin
                  GROUP BY TO_CHAR (dateprev, 'YYYY-MM')
                  UNION ALL
                  SELECT   TO_CHAR (datereal, 'YYYY-MM'), 0, COUNT (*)
                      FROM activites
                     WHERE PLAN = :v_plan
                       AND datereal BETWEEN :datedeb AND :datefin
                  GROUP BY TO_CHAR (datereal, 'YYYY-MM'))
        GROUP BY mois) projet

Prend le sous-select comme une vue qui te donne 2 lignes par mois (où il y a au moins une date prev ou real). Après tu peux en faire ce que tu veux en 1 seul SELECT FROM VUE GROUP BY MOIS

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT mois, SUM(nb_prev) AS prévu, SUM(nb_real) AS réalisé, 
sum(nb_prev) + sum(nb_real) as total
FROM (
	SELECT TO_CHAR(dateprev, 'YYYY-MM') AS mois, COUNT(*) AS nb_prev, 0 AS nb_real
	FROM ACTIVITES
	WHERE PLAN = :v_plan
	AND dateprev BETWEEN :datedeb AND :datefin
	GROUP BY TO_CHAR(dateprev, 'YYYY-MM')
	UNION ALL
	SELECT TO_CHAR(datereal, 'YYYY-MM'), 0, COUNT(*) 
	FROM ACTIVITES
	WHERE PLAN = :v_plan
	AND datereal BETWEEN :datedeb AND :datefin)
	GROUP BY TO_CHAR(datereal, 'YYYY-MM')
	) 
GROUP BY mois

20/03/2007, 13h22
gg2vig

cumul progressif

je ne parlaiis pas de cumuler réalisés avec prévus

mais séparement les réalisés et les prévus

de datedeb à chaque occurence des mois suivant jusqu'à detefin

par exemple si on prend datedeb=01/01/2006 et datefin = 31/12/2006

mois prevus réalisés

01 X01 Y01
02 (X02+X01) (Y02+Y01)
03 (X01+X02+X03) (Y01+Y02+Y03)
04 etc
20/03/2007, 14h12
McM

Ben voila, avec un petit exemple.. on cherche moins ;)

Les fonctions analytiques seules peuvent faire celà (après faut voir en 8i ce qui est faisable):
http://lalystar.developpez.com/fonctionsAnalytiques/