Problème de création de vue en SQLSERVER

Version imprimable

Bonjour,

Je suis débutant en SQLSERVER, j'ai une vue à créer avec un calcul particulier, et je ne sais pas comment faire, et savoir si c'est possible....
J'ai créé une première vue assez simple qui stocke des montants associés à une personne sur une périodicité de mois , trimestre semestre, année. (voilà la structure);)
Plusieurs dossiers par mois pour une personne (donc plusieurs lignes)
Code:

1 2 SELECT [IdDossier], [Nom], [Prenom], [Nom_Complet], [MOIS], [Libelle_Mois],[TRIMESTRE],[SEMESTRE],[ANNEE],[MTTTC],[StadeD] FROM V_CA_Detail_Agent
J'aimerai faire une 2ème vue pour faire un calcul particulier.....
J'aurai aimé en sortie de cette 2ème vue, n'avoir plus qu'une seule ligne par "Nom" pour un mois (jusqu'à la ça va;))
Code:

1 2 SELECT [Nom],[Prenom],[MOIS],[Libelle_Mois],[TRIMESTRE],[SEMESTRE],[ANNEE],[Montant],[Objectif] FROM V_CA_Mensuel_Agent
Mais pour chaque ligne de cette vue, j'ai une colonne supplémentaire qui est l'objectif
Cet objectif est calculé de la façon suivante:
l'objectif du mois janvier 2015 est calculé par rapport aux "MTTTC" des 2 trimestres précédents (mois "6,7,8,9,10,11,12" trimestre "2 et 3" semestre "2" année "2014")
février 2015 est calculé par rapport aux "MTTTC" des 2 trimestres précédents (mois "6,7,8,9,10,11,12" trimestre "2 et 3" semestre "2" année "2014")
mars 2015 est calculé par rapport aux "MTTTC" des 2 trimestres précédents (mois "6,7,8,9,10,11,12" trimestre "2 et 3" semestre "2" année "2014")
avril 2015 est calculé par rapport aux "MTTTC" des 2 trimestres précédents (mois "10,11,12" trimestre "4" semestre "2" année "2014" et mois "1,2,3" trimestre "1" semestre "1" année"2015")
mai 2015 est calculé par rapport aux "MTTTC" des 2 trimestres précédents (mois "10,11,12" trimestre "4" semestre "2" année "2014" et mois "1,2,3" trimestre "1" semestre "1" année"2015").........

L'objectif doit être la moyenne de ces 6 mois (SUM(MTTTC)/6) majoré de 10%

Exemple des résultats pour l'objectif
NOM	       PRENOM	MOIS 	LIBELLE_MOIS	TRIMESTRE	SEMESTRE	 ANNEE	MONTANT	OBJECTIF
Dupont	Marc	           1	            Janvier	               1	               1	  2014	   6500	
Dupont	Marc	           2	            Février	               1	               1	  2014	   3500	
Dupont	Marc	           3	            Mars	               1                      1	  2014	   6250	
Dupont	Marc	           4	            Avril	               2	               1	  2014	   9400	
Dupont	Marc	           5	            Mai	               2	               1         2014	  12500	
Dupont	Marc	           6	            Juin	               2	               1	  2014	   6230	
Dupont	Marc	           7	            Juillet	               3	               2	  2014	   4560	
Dupont	Marc	           8	            Août	               3	               2	  2014	   9630	
Dupont	Marc	           9	            Septembre	       3	               2	  2014	   7500	
Dupont	Marc	          10	            Octobre	               4	               2	  2014	   5370	
Dupont	Marc	          11	            Novembre	       4	               2	  2014	   3240	
Dupont	Marc	          12	            Décembre	       4	               2	  2014	   1110	
Dupont	Marc	           1	            Janvier	               1	               1	  2015	   6700	   5758
Dupont	Marc	           2	            Février	               1	               1	  2015	   10530	   5758
Dupont	Marc	           3	            Mars	               1	               1	  2015	   5480	   5758
Dupont	Marc	           4	            Avril	               2	               1	  2015	   6600	   5945
Dupont	Marc	           5	            Mai	               2	               1	  2015	   7720	   5945
Dupont	Marc	           6	            Juin	               2	               1	  2015	   8840	   5945
Dupont	Marc	           7	            Juillet	               3	               2	  2015	   9960	   8409
J'espère avoir été assez clair, merci pour votre aide éventuelle.

Cordialement.

Rodolphe

29/11/2015, 11h55
tse_jc

Bonjour,

Il vous faut refaire vos calcul d'aggrégats à partir de vos données sources (colonnes de type datetime) en utilisant DATEDIFF.

Bon week-end.
29/11/2015, 12h36
rodolphelami

Re,

Est-ce qu'il est possible de me donner un exemple...

J'étais partie sur des CASE WHEN MOIS=1 OR MOIS=2 OR MOIS=3

mais ça ne fonctionne pas, je démarre en SQLSERVER, j'ai essayé de chercher sur le NET, mais je ne trouve pas de cas similaire, j'ai beau lire pas de posts je ne trouve :cry:

@++
30/11/2015, 11h32
tse_jc

Bonjour,

Pourriez-vous nous fournir la structure des tables qui contiennent les données dont sont issues celles de vos vues actuelles?

Merci

2 pièce(s) jointe(s)

Bonsoir,

Je ne sais pas si cela peut vous aider
Code:

1 2 3 4 5 6 SELECT <IdDossier, int,> ,<IdAppFlux, int,> ,<IdMandant, int,> ,<TypeFlux, smallint,> ,<MontantHT, float,> ,<MontantTTC, float,> ,<MontantTVA, float,> ,<NumFacture, varchar(15),> ,<DateFactPrevu, datetime,> ,<DateFacture, datetime,> ,<IsRegle, bit,> ,<MontantRegle1, float,> ,<MontantRegle2, float,> ,<MontantRegle3, float,> ,<DateEcheance1, datetime,> ,<DateEcheance2, datetime,> ,<DateEcheance3, datetime,> ,<DateRegle1, datetime,> ,<DateRegle2, datetime,> ,<DateRegle3, datetime,> ,<InfoRegle1, varchar(50),> ,<InfoRegle2, varchar(50),> ,<InfoRegle3, varchar(50),> ,<TypeMP1, smallint,> ,<TypeMP2, smallint,> ,<TypeMP3, smallint,>,<CIdEnreg, int,>,<Cts, binary(8),>) FROM dbo.TFlux
Pièce jointe 193965

C'est pour cela que j'ai crée une vue afin d'avoir toutes les informations pour faire ce calcul un peu particulier....

Pièce jointe 193966

J'aimerai donc dans une autre vue ou dans la même ajouter la valeur de l'objectif fixé comme décrit sur la partie haute du post.

Cordialement.

Rodolphe

01/12/2015, 10h46
tse_jc

Bonjour,

Merci pour ces précisions. J'ai une dernière question. Dans votre table tflux, quelle est la colonne de type datetime qui sert de date de référence pour la prise en compte du CA calculé?

Bonjour,

Malheureusement, elle ne vient pas de cette table, elle vient de la table TDossier, pour créer ma vue j'avais fait une jointure avec cette table sur l'identifiant du dossier et la table TAgent pour récupérer les informations du conseiller....

Le code SQL de la création de ma vue pour avoir une ligne par conseiller par dossier.

Code:

1
2
3
4
5
6
7
select dbo.TFlux.IdDossier, Nom ,Prenom, (Nom + '  ' + Prenom) AS Nom_Complet,MONTH(DateRepClient) AS MOIS, DATENAME(MONTH,DateRepClient)AS Libelle_Mois, DATEPART(q,DateRepClient) AS TRIMESTRE,(CASE WHEN DATEPART(MONTH,DateRepClient)BETWEEN 7 AND 12 THEN 2 ELSE 1 END) AS SEMESTRE, YEAR(DateRepClient) AS ANNEE,  SUM(CASE WHEN TypeFlux<3 THEN MontantTTC ELSE MontantTTC*-1 END) AS MTTTC, 
StadeD
FROM dbo.TFlux
LEFT JOIN dbo.TDossier ON TFlux.IdDossier = dbo.TDossier.IdDossier
LEFT JOIN dbo.TAgent ON  dbo.TDossier.IdAgent = dbo.TAgent.IdAgent
WHERE StadeD BETWEEN 5 AND 9
GROUP BY dbo.TFlux.IdDossier,Nom,Prenom,YEAR(DateRepClient),MONTH(DateRepClient),StadeD,DATEPART(q,DateRepClient),DATENAME(MONTH,DateRepClient)

Cordialement.

Bonjour,

Finalement, je pense que sur SQL Server 2008 vous risquez d'être obligé de passer par un modèle tel que proposé par SQL Pro http://sqlpro.developpez.com/cours/gestiontemps/

Sinon il y aurait une alternative à partir de SQL Server 2012 utilisant des fonctions fenêtrées comme suit:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT t0.IdDossier,t1.Nom,t1.Prenom,(t1.Nom+' '+t1.Prenom) AS Nom_Complet,SUM(CASE WHEN t3.TypeFlux<3 THEN t0.MontantTTC ELSE t0.MontantTTC*-1)AS MTTC,(SUM(t1.CA0+t1.CA1+t1.CA2+t1.CA3+t1.CA4+t1.CA5)/6)*1.1 AS objectif
FROM dbo.TFlux AS t0
LEFT JOIN dbo.TDossier AS t2 ON t0.IdDossier=t2.IdDossier
INNER JOIN dbo.TAgent AS t1 ON t2.IdAgent=t1.IdAgent
LEFT JOIN
(SELECT t2.IdDossier,MONTH(t2.DateRepClient) AS CA_MONTH, YEAR(t2.DateRepClient) AS CA_YEAR,
SUM(CASE WHEN t1.TypeFlux<3 THEN t2.MontantTTC ELSE t2.MontantTTC*-1) AS CA,
LEAD(SUM(CASE WHEN t1.TypeFlux<3 THEN t2.MontantTTC ELSE t2.MontantTTC*-1),1,0)OVER(PARTITION BY t2.IdDossier, YEAR(t2.DateRepClient) ORDER BY YEAR(t2.DateRepClient) DESC,MONTH(t2.DateRepClient) DESC) AS CA1,
LEAD(SUM(CASE WHEN t1.TypeFlux<3 THEN t2.MontantTTC ELSE t2.MontantTTC*-1),2,0)OVER(PARTITION BY t2.IdDossier, YEAR(t2.DateRepClient) ORDER BY YEAR(t2.DateRepClient) DESC,MONTH(t2.DateRepClient) DESC) AS CA2,
LEAD(SUM(CASE WHEN t1.TypeFlux<3 THEN t2.MontantTTC ELSE t2.MontantTTC*-1),3,0)OVER(PARTITION BY t2.IdDossier, YEAR(t2.DateRepClient) ORDER BY YEAR(t2.DateRepClient) DESC,MONTH(t2.DateRepClient) DESC) AS CA3,
LEAD(SUM(CASE WHEN t1.TypeFlux<3 THEN t2.MontantTTC ELSE t2.MontantTTC*-1),4,0)OVER(PARTITION BY t2.IdDossier, YEAR(t2.DateRepClient) ORDER BY YEAR(t2.DateRepClient) DESC,MONTH(t2.DateRepClient) DESC) AS CA4,
LEAD(SUM(CASE WHEN t1.TypeFlux<3 THEN t2.MontantTTC ELSE t2.MontantTTC*-1),5,0)OVER(PARTITION BY t2.IdDossier, YEAR(t2.DateRepClient) ORDER BY YEAR(t2.DateRepClient) DESC,MONTH(t2.DateRepClient) DESC) AS CA5
FROM dbo.TDossier AS t2
INNER JOIN dbo.TFlux AS t1 ON t2.IdDossier=t3.IdDossier
GROUP BY t2.IdDossier,t2.TypeFlux,MONTH(t2.DateRepClient),YEAR(t2.DateRepClient),t2.MontantTTC) AS t3 ON t0.IdDossier=t3.IdDossier
ORDER BY YEAR(t2.DateRepClient) DESC, MONTH(t2.DateRepClient) DESC;

Bonne journée

03/12/2015, 00h34
rodolphelami

Bonsoir,

Merci beaucoup pour tout, je vais essayer, malheureusement, je ne peux pas changer de version de SQL, je ne sais pas si le client final sera d'accord.

Très cordialement.

Bravo pour toute cette attention.

Rodolphe.