pb date de référence sur double comptage

[gg2vig]

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

[salim11]

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

[McM]

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

[gg2vig]

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?

[McM]

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

[gg2vig]

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

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

[salim11]

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

[McM]

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

[gg2vig]

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

[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/