1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
|
SELECT { fn CONCAT(m.libelle_en, CAST(h.Annee AS varchar)) } AS Date, en.libelle_niveau_2 AS lib_en,
(SELECT h2.Montant
FROM headcount AS h2 INNER JOIN
nomenclature_en AS en2 ON en2.Nomenclature = h2.Nomenclature
WHERE (h2.Mois = h.Mois) AND (h2.Annee = h.Annee) AND (en2.id_niveau_1 = en.id_niveau_1) AND (h2.Nomenclature = h.Nomenclature))
/
(SELECT SUM(h.Montant) AS sum
FROM headcount AS h3 INNER JOIN
nomenclature_en AS en3 ON en3.Nomenclature = h3.Nomenclature
WHERE (h3.Mois = h.Mois) AND (h3.Annee = h.Annee) AND (en3.id_niveau_1 = en.id_niveau_1) AND (h3.Nomenclature = h.Nomenclature))
AS div
FROM headcount AS h INNER JOIN
nomenclature_en AS en ON h.Nomenclature = en.Nomenclature INNER JOIN
month_name AS m ON h.Mois = m.Numero
WHERE (en.id_niveau_1 = 'DIV') AND (h.Annee = @minYear) AND (h.Mois >= @month) OR
(en.id_niveau_1 = 'DIV') AND (h.Annee > @minYear) AND (h.Annee < @maxYear) OR
(en.id_niveau_1 = 'DIV') AND (h.Annee = @maxYear) AND (h.Mois <= @month)
GROUP BY h.Annee, h.Mois, en.libelle_niveau_1, m.libelle_en, en.libelle_niveau_2, h.Nomenclature, en.id_niveau_1
ORDER BY h.Annee, h.Mois |
Partager