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
|
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `location_generer_statistiques_saison`(date_debut CHAR(10),date_fin CHAR(10),saison CHAR(10))
BEGIN
SELECT id_evenement, logement,
SUM(total_demandes_enregistrees) AS total_demandes_enregistrees,
SUM(total_demandes_attribuees) AS total_demandes_attribuees,
SUM(total_journees_attribuees) AS total_journees_attribuees,
SUM(total_semaines_attribuees) AS total_semaines_attribuees,
SUM(total_journees) AS total_journees,
SUM(total_semaines) AS total_semaines,
SUM(total_journees) - SUM(total_journees_attribuees) AS total_journees_difference,
SUM(total_semaines) - SUM(total_semaines_attribuees) AS total_semaines_difference,
IF(ROUND(100 - ((SUM(total_journees) - SUM(total_journees_attribuees)) / SUM(total_journees) * 100)) IS NULL ,0,ROUND(100 - ((SUM(total_journees) - SUM(total_journees_attribuees)) / SUM(total_journees) * 100))) AS ratio_remplissage
FROM (
SELECT `d`.`id_evenement` AS `id_evenement`,REPLACE(`e`.`libelle`,'- NUITEE','') AS `logement`, 0 AS `total_demandes_enregistrees`,IF(isnull(sum(`d`.`demandes`)),0,sum(`d`.`demandes`)) AS `total_demandes_attribuees`,sum(IF((`d`.`journees` = 8),(`d`.`journees` - 1),`d`.`journees`)) AS `total_journees_attribuees`,TRUNCATE((sum(IF((`d`.`journees` = 8),(`d`.`journees` - 1),`d`.`journees`)) / 7),0) AS `total_semaines_attribuees`,0 AS `total_journees`,0 AS `total_semaines`
FROM (`location_demandes_synthese` `d` LEFT JOIN `evenement` `e` ON((`e`.`id_evenement` = `d`.`id_evenement`)))
WHERE ((`d`.`date_debut` BETWEEN date_debut AND date_fin) AND (`d`.`id_evenement_demande_statut` IN (1,11)) AND (`d`.`date_debut` < date_fin))
GROUP BY REPLACE(`e`.`libelle`,'- NUITEE','')
union ALL
SELECT `id_evenement` AS `id_evenement`,REPLACE(`libelle`,'- NUITEE','') AS `logement`, 0 AS `total_demandes_enregistrees`,0 AS `total_demandes_attribuees`,0 AS `total_journees_attribuees`,0 AS `total_semaines_attribuees`,sum(`duree`) AS `total_journees`, TRUNCATE((sum(`duree`) / 7),0) AS `total_semaines`
FROM `location_periodes_synthese`
WHERE ((`saison` = saison) AND (((`id_evenement_type` = 2) AND (`saison` IS NOT NULL)) OR (`id_evenement_type` = 4)))
GROUP BY REPLACE(`libelle`,'- NUITEE','')
union ALL
SELECT `d`.`id_evenement` AS `id_evenement`,REPLACE(`e`.`libelle`,'- NUITEE','') AS `logement`, IF(isnull(sum(`d`.`demandes`)),0,sum(`d`.`demandes`)) AS `total_demandes_enregistrees`, 0 AS `total_demandes_attribuees`, 0 AS `total_journees_attribuees`, 0 AS `total_semaines_attribuees`, 0 AS `total_journees`,0 AS `total_semaines`
FROM (`location_demandes_synthese` `d` LEFT JOIN `evenement` `e` ON((`e`.`id_evenement` = `d`.`id_evenement`)))
WHERE ((`d`.`date_debut` BETWEEN date_debut AND date_fin) AND (`d`.`date_debut` < date_fin))
GROUP BY REPLACE(`e`.`libelle`,'- NUITEE',''))
travail
GROUP BY logement;
END |
21/09/2011, 11h42
Julien SIMON
Différence entre la somme de ma procédure et de la requête
00
