From 2295d18677c8500c8b47374c06f93106f5c42074 Mon Sep 17 00:00:00 2001 From: Eric Mc Sween Date: Thu, 24 Nov 2011 10:45:49 -0500 Subject: [PATCH] =?utf8?q?Proc=C3=A9dure=20pour=20l'interrogation=20de=20cum?= =?utf8?q?ul?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- auf_p_p4_intero_cumul.sql | 169 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 169 insertions(+) create mode 100644 auf_p_p4_intero_cumul.sql diff --git a/auf_p_p4_intero_cumul.sql b/auf_p_p4_intero_cumul.sql new file mode 100644 index 0000000..49c9b82 --- /dev/null +++ b/auf_p_p4_intero_cumul.sql @@ -0,0 +1,169 @@ + + + + +--exec auf_p_p4_intero_cumul 2011,1,12,'91060PJ','','','' + + +CREATE procedure auf_p_p4_intero_cumul + + @annee int , + @periodeD int, + @periodeF int, + @projet varchar(7), + @region varchar(12), + @implantation varchar(12), + @el1 varchar(6) + + +as + + +SET NOCOUNT ON + + +create table #cumuls (el1 varchar(10), + el3 varchar(12), + balcode varchar(12), + nature varchar(30), + montant money null) + + +insert into #cumuls + +select + el1, + el3, + balcode , + case when el1 like '97%' then + 'Recette' + when el1 like '2%' then + 'Dépense' + when (el1 like '1%' AND el3 like '9003%') then + 'Dépense' + when (el1 like '1%' AND el3 not like '9003%') then + 'Recette' + when el1 like '6%' then + 'Dépense' + when el1 like '7%' then + 'Recette' + when el1 like '86%' then + 'Dépense BSTG' + when el1 like '87%' then + 'Recette BSTG' + when el1 like '987%' then + 'Recette BSTG' + when el1 like '986%' then + 'Dépense BSTG' + else + 'Dépense' + end , + sum(full_value) mont + + +from oas_balance + +where yr = @annee and + period between @periodeD and @periodeF and + curcode = 'EUR' and + repbasis = 5 and + balcode not like 'DBM%' and + el3 like @projet + '%' and + el1 not like '4%' and + el1 like @el1 + '%' +------------------------------------- + +group by el1,el3, balcode + + + +select @annee exercice, + cumul.el1, + cumul.el3, + groupe.nom_court, + groupe.nom, + cumul.nature, + groupe.region, + groupe.implantation, + --groupe.implantation_nc, + + sum(isnull(budget.montant,0)) budget, + sum(isnull(estime.montant,0)) estime, + sum(isnull(mensul.montant,0)) mensul, + sum(isnull(reel.montant,0)) reel , + sum(isnull(engage.montant,0)) engage + +from auf_v_p4_projets_groupes groupe, + + ( select distinct el1,el3,nature + from #cumuls ) cumul left join + ( select el1, + el3, + balcode, + montant + from #cumuls) budget + + on ( cumul.el1 = budget.el1 and + cumul.el3 = budget.el3 and + budget.balcode = 'BUDGET' ) + + left join + ( select el1, + el3, + balcode, + montant + + from #cumuls) estime + + on ( cumul.el1 = estime.el1 and + cumul.el3 = estime.el3 and + estime.balcode = 'ESTIME' ) + + left join + ( select el1, + el3, + balcode, + montant + + from #cumuls) mensul + + on ( cumul.el1 = mensul.el1 and + cumul.el3 = mensul.el3 and + mensul.balcode = 'BUDGET-MENS' ) + + left join + ( select el1, + el3, + balcode, + montant + + from #cumuls) engage + + on ( cumul.el1 = engage.el1 and + cumul.el3 = engage.el3 and + engage.balcode = 'ENGAGE' ) + + left join + ( select el1, + el3, + balcode, + montant + from #cumuls) reel + + on ( cumul.el1 = reel.el1 and + cumul.el3 = reel.el3 and + reel.balcode = 'REEL' ) + +where cumul.el3 = groupe.code_budgetaire and + groupe.region like @region + '%' and + groupe.implantation like 'I' + rtrim(ltrim(@implantation)) + '%' + +group by cumul.el1, + cumul.el3, + groupe.nom_court, + groupe.nom, + cumul.nature, + groupe.region , + groupe.implantation + --groupe.implantation_nc +GO -- 1.7.10.4