2 Boucles WHILE imbriquées pour obtenir des lignes de sous-totaux sur 2 colonnes

**KobaltK** · 09/09/2016, 11h21

Bonjour,

Je suis coincé !!! et débutant...

Mon objectif est donc d'insérer des lignes de sous-totaux dans deux colonnes. Jusqu'à présent, j'ai pu depuis une table temporaire PIVOT et avec une boucle WHILE basique obtenir des ligne de sous-totaux pour une colonne mais impossible d'y insérer un autre WHILE pour créer des sous-totaux imbriqués pour une deuxième colonne.

Merci pour votre aide, il doit sans doute y avoir d'autres solutions.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
while (select IdCE from CE where IdCE = @I) = @I
begin
		set @NomCE = (select NomCE from CE where IdCE = @I)
 
		insert into #whileCE
			SELECT CE,R1,R2,E,C,T -- je dois obtenir des lignes sous-total sur R1
			  FROM #pivot PT
			  join CE C
			  on C.NomCE = PT.CE
			  where C.IdCE = @I
		union all
			select 'Total'+' '+@NomCE,'','',sum(E),sum(C),sum(T)
			  FROM #pivot PT
			  join CE C
			  on C.NomCE = PT.CE
			  where C.IdCE = @I
		set @I= @I+1
end

**Bernardos** · 09/09/2016, 11h56

Bonjour,
Règles de postage à lire impérativement avant de poster
tu y trouveras comment générer les requêtes DDL.
Sois plus précis sur ce que tu veux faire.
cordialement,

**KobaltK** · 09/09/2016, 13h08

Je souhaite obtenir cette ligne sous total supplémentaire.
Merci

CE	Rang 1	Rang 2	Encadrants	Compagnons	TOTAL
CC	GOYER		2	3	5
Total CC			2	3	5
GO INFRA	BOTTE FONDATIONS SOLETANCHE		1	2	3
GO INFRA	BOTTE FONDATIONS SOLETANCHE	ECR ENVIRONNEMENT IDF	0	1	1
	TOTAL BOTTE FONDATIONS SOLETANCHE		1	3	4
...
Total GO INFRA			1	8	9

Quant à l'amélioration de la lisibilité du bout de requête, je ne sais pas comment faire.

**Bernardos** · 09/09/2016, 13h17

Les requêtes DDL c'est ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE [dbo].[CATEGORIE](
	[IDCATEGORIE] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[NOMCATEGORIE] [nvarchar](63) NULL,
	[DESCRIPTION] [nvarchar](max) NULL,
 CONSTRAINT [PK_CATEGORIE] PRIMARY KEY CLUSTERED 
(
	[IDCATEGORIE] ASC
))

ca nous permet de pouvoir tester chez nous les requêtes
Cordialement,

**KobaltK** · 09/09/2016, 13h44

J'ai fais au mieux.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
USE [VISOR]
GO
SET LANGUAGE FRENCH
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
DECLARE
@I INT = 1,
@J INT = 1,
@NOMCE NVARCHAR(MAX) = '',
@NOMRANG1 NVARCHAR(MAX) = ''
 
IF OBJECT_ID ('TEMPDB..#PIVOT') IS NOT NULL
DROP TABLE #PIVOT
 
CREATE TABLE #PIVOT -- TABLE TEMPORAIRE POUR LE PIVOT
(
	CE NVARCHAR(MAX),
	R1 NVARCHAR(MAX),
	R2 NVARCHAR(MAX),
	E INT,
	C INT,
	T INT
);
 
IF OBJECT_ID ('TEMPDB..#WHILECE') IS NOT NULL
DROP TABLE #WHILECE
 
CREATE TABLE #WHILECE -- TABLE TEMPORAIRE POUR LA BOUCLE WHILE DEPUIS TABLE PIVOT ET AJOUT LIGNE TOTAL GÉNÉRAL
(
	CE NVARCHAR(MAX),
	[RANG 1] NVARCHAR(MAX),
	[RANG 2] NVARCHAR(MAX),
	ENCADRANTS INT,
	COMPAGNONS INT,
	TOTAL INT
);
 
 
WITH REQUETE AS -- VUE DE LA REQUETE DE BASE
(
SELECT DISTINCT
		E.CHAMP4 AS [CE],
		ISNULL(S.GERANT, S.LIBELLE) AS [RANG 1], -- PERMUTE ENTREPRISE ET ST POUR RANG 1
			CASE
			WHEN (S.GERANT IS NULL)
			THEN ''
			WHEN (S.GERANT IS NOT NULL)
			THEN S.LIBELLE
			END AS [RANG 2], -- PERMUTE ENTREPRISE ET ST POUR RANG 2
 
		E.CHAMP2 AS FONCTION,
		COUNT(DISTINCT E.IDUTILISATEUR) AS TL -- COLONNE TOTAL
	FROM
		EVENEMENTS E
		FULL JOIN
		SOCIETES S ON E.IDSOCIETE = S.IDSOCIETE
		FULL JOIN
		LECTEURS L ON E.IDLECTEUR = L.IDLECTEUR
		FULL JOIN
		UTILISATEURS U ON E.IDUTILISATEUR = U.IDBASE
	WHERE
		--E.DATEEVENEMENT > CAST(CONVERT(VARCHAR,DATEADD(DD,0,'08/09/2016'),103) + ' ' + '11:30' AS SMALLDATETIME) AND E.DATEEVENEMENT <  CAST(CONVERT(VARCHAR,'08/09/2016',103) + ' ' + '12:00' AS SMALLDATETIME)
		--AND
		(L.APB =1)
		AND  U.PRESENCE = 1 -- SELECTION TOURNIQUETS EN ENTRÉE VIA ANTI PASSBACK ET PRESENCE
		AND S.IDSOCIETE <> 150
	GROUP BY E.CHAMP4,S.LIBELLE,S.GERANT,E.CHAMP2
)
 
INSERT INTO #PIVOT 
 
SELECT COALESCE(CE,''), COALESCE([RANG 1],''), COALESCE([RANG 2],''), COALESCE([ENCADRANT],''), COALESCE([COMPAGNON],''), SUM(COALESCE([ENCADRANT],'')+COALESCE([COMPAGNON],''))
	FROM REQUETE
		PIVOT
		(SUM(TL) FOR FONCTION IN ([ENCADRANT],[COMPAGNON])) AS P	-- RESULTAT DU PIVOT
	GROUP BY CE,[RANG 1],[RANG 2],ENCADRANT,COMPAGNON
 
--SELECT * FROM #PIVOT
 
WHILE (SELECT IDCE FROM CE WHERE IDCE = @I) = @I
BEGIN
	SET @NOMCE = (SELECT NOMCE FROM CE WHERE IDCE = @I)
 
	INSERT INTO #WHILECE
		SELECT CE,R1,R2,E,C,T
			FROM #PIVOT PT
				JOIN CE C
				ON C.NOMCE = PT.CE
			WHERE C.IDCE = @I
		UNION ALL
		SELECT 'TOTAL'+' '+@NOMCE,'','',SUM(E),SUM(C),SUM(T)
			FROM #PIVOT PT
				JOIN CE C
				ON C.NOMCE = PT.CE
			WHERE C.IDCE = @I
	SET @I= @I+1
END
 
SELECT * FROM #WHILECE
UNION ALL
SELECT 'TOTAL GENERAL','','',SUM(ENCADRANTS)/2,SUM(COMPAGNONS)/2,SUM(TOTAL)/2
	FROM #WHILECE

**François DORIN** · 09/09/2016, 14h08

Bonjour,

Connais-tu ROLLUP ? Si tu souhaites avoir des totaux, sous-totaux, etc... c'est sans doute ce qu'il te faut.

De même, il serait bon que l'on ait les données de bases, sur lesquelles tu t'appuies pour réaliser tous tes calculs, car j'ai l'impression que ta solution à coup de WHILE est très complexe et contre-performante. Tu ne nous as fournis que les tables temporaires sur lesquels tu t'appuies.

Le mieux, pour obtenir de l'aide, c'est :

Préciser le schéma de ta base ;
Avoir des données ;
Montrer le résultat souhaité ;
Nous fournir des scripts pour générer schéma + données (merci Bernardos);
Nous faire part des difficultés rencontrées, éventuellement en postant la ou les ébauche(s) de solution sur lesquelles tu travailles.

**aieeeuuuuu** · 09/09/2016, 14h13

Bonjour,

Il me semble qu'un ROLLUP devrait suffire si j'ai bien compris votre besoin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
WITH REQUETE AS -- VUE DE LA REQUETE DE BASE
(
SELECT DISTINCT
		E.CHAMP4 AS [CE],
		ISNULL(S.GERANT, S.LIBELLE) AS [RANG 1], -- PERMUTE ENTREPRISE ET ST POUR RANG 1
			CASE
			WHEN (S.GERANT IS NULL)
			THEN ''
			WHEN (S.GERANT IS NOT NULL)
			THEN S.LIBELLE
			END AS [RANG 2], -- PERMUTE ENTREPRISE ET ST POUR RANG 2
 
		E.CHAMP2 AS FONCTION,
		COUNT(DISTINCT E.IDUTILISATEUR) AS TL -- COLONNE TOTAL
	FROM
		EVENEMENTS E
		FULL JOIN
		SOCIETES S ON E.IDSOCIETE = S.IDSOCIETE
		FULL JOIN
		LECTEURS L ON E.IDLECTEUR = L.IDLECTEUR
		FULL JOIN
		UTILISATEURS U ON E.IDUTILISATEUR = U.IDBASE
	WHERE
		--E.DATEEVENEMENT > CAST(CONVERT(VARCHAR,DATEADD(DD,0,'08/09/2016'),103) + ' ' + '11:30' AS SMALLDATETIME) AND E.DATEEVENEMENT <  CAST(CONVERT(VARCHAR,'08/09/2016',103) + ' ' + '12:00' AS SMALLDATETIME)
		--AND
		(L.APB =1)
		AND  U.PRESENCE = 1 -- SELECTION TOURNIQUETS EN ENTRÉE VIA ANTI PASSBACK ET PRESENCE
		AND S.IDSOCIETE <> 150
	GROUP BY E.CHAMP4,S.LIBELLE,S.GERANT,E.CHAMP2
) 
SELECT COALESCE(CE,''), COALESCE([RANG 1],''), COALESCE([RANG 2],''), COALESCE([ENCADRANT],''), COALESCE([COMPAGNON],''), SUM(COALESCE([ENCADRANT],'')+COALESCE([COMPAGNON],''))
	FROM REQUETE
		PIVOT
		(SUM(TL) FOR FONCTION IN ([ENCADRANT],[COMPAGNON])) AS P	-- RESULTAT DU PIVOT
	GROUP BY CE,[RANG 1],[RANG 2],ENCADRANT,COMPAGNON
WITH ROLLUP

Il restera éventuellement à remplacer les NULL générés par la valeur de votre choix.

**KobaltK** · 09/09/2016, 14h37

Merci mais le ROLLUP ne donnait pas les résultats attendus, c'est pourquoi je suis passé par une (des) boucle(s) WHILE.

**Bernardos** · 09/09/2016, 14h50

si tu nous donnes le script de création de tes tables de bases (pas les table temporaire) avec un jeu de données (insert) et que tu nous expliques le résultat attendu (mais ca je pense que j'ai compris),
on va pouvoir te proposer de belles requêtes qui n'utiliseront pas ces affreuses tables temporaires

Cordialement,

**KobaltK** · 09/09/2016, 15h56

Pour le jeu de données INSERT, je ne sais pas comment vous le fournir !..

**François DORIN** · 09/09/2016, 17h52

Pour générer un script avec schéma et données, tu peux, depuis SQL Server Management Studio :

te connecter à ton instance ;
faire un clic droit sur ta base de données et sélectionner "Tâches > Générer des scripts" ;
tu pourras alors choisir les éléments à exporter. Tout, ou juste une partie. Pour nous, juste les tables nécessaires "societe" et "evenement" par exemple.
Après avoir choisi les tables, tu cliques sur suivant
Sur cette nouvelle page, il y a un bouton "Avancé". Clique dessus
Dans la fenêtre qui s'ouvre, au niveau de "Type de données à inclure dans le script", spécifie bien "schéma et données".
tu cliques sur ok pour fermer la petite fenêtre
tu cliques sur suivant pour afficher le résumé des opérations
tu cliques sur suivant pour réaliser l'exportation

A noter que cette méthode fonctionne très bien pour de petits jeux de données. Si tu as beaucoup de données, il te faut extraire un jeu de données utiles avant de nous le communiquer (on n'a pas besoin d'avoir 150000 enregistrements dans chaque table pour aider à répondre à une question

)

**KobaltK** · 09/09/2016, 19h06

C'est bon j'ai procédé à l'extraction !
Cependant, comment je vous le poste ??

**KobaltK** · 09/09/2016, 19h37

C'est bon, voici le script attendu. Petit souci avec Firefox qui ne m'ouvrait pas la fenêtre pièce-jointe

script.zip

**Bernardos** · 09/09/2016, 19h38

en principe c'est un script avec des insert que tu as obtenu, non?

**KobaltK** · 09/09/2016, 19h43

Envoyé par Bernardos

en principe c'est un script avec des insert que tu as obtenu, non?

J'ai appliqué les recommandations de dorinf.

Il y a les CREATE et INSERT.

J'ai joint le résultat de mon travail (code déjà posté) sous forme de procédure stockée également.

**François DORIN** · 09/09/2016, 20h17

Merci, j'ai pu créer une base de test !

Voici un script

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH REQUETE AS (
SELECT DISTINCT	E.Champ4 AS CE, 
		S.Libelle, 
		ISNULL(S.GERANT, S.LIBELLE) AS [RANG 1], -- PERMUTE ENTREPRISE ET ST POUR RANG 1
		CASE
			WHEN (S.GERANT IS NULL)
				THEN ''
			WHEN (S.GERANT IS NOT NULL)
				THEN S.LIBELLE
		END AS [RANG 2], -- PERMUTE ENTREPRISE ET ST POUR RANG 2
		E.Champ2 AS Fonction,
		E.IdUtilisateur
	FROM Evenements AS E
	INNER JOIN Societes AS S ON E.IdSociete = S.IdSociete)
SELECT	R.CE, 
		R.[RANG 1],
		R.[RANG 2],
		SUM(CASE WHEN R.Fonction = 'Encadrant' THEN 1 ELSE 0 END) AS Encadrant, 
		SUM(CASE WHEN R.Fonction = 'Compagnon' THEN 1 ELSE 0 END) AS Compagnon
	FROM REQUETE AS R
	GROUP BY R.CE, R.[RANG 1], R.[RANG 2] WITH ROLLUP

J'obtiens a priori ce que tu attends, sauf que 3 compagnons pour RANG1='BOTTE FONDATIONS SOLETANCHE' et RANG2=''.

Pas de while, juste un ROLLUP ! Ou alors je n'ai pas compris ce que tu attendais...

Extrait des résultats :

GO INFRA	BOTTE FONDATIONS SOLETANCHE		                1	3
GO INFRA	BOTTE FONDATIONS SOLETANCHE	ECR ENVIRONNEMENT IDF	0	1
GO INFRA	BOTTE FONDATIONS SOLETANCHE	NULL	                1	4

Il manque le total (encadrant + compagnon), mais je te laisse le calcul

[EDIT]
La différence dans le calcul vient certainement du fait que de mon côté, j'ai supprimé la clause WHERE lorsque j'ai défini la CTE Requete. N'ayant pas toutes les tables, je ne pouvais pas garder les contraintes.
[/EDIT]

**KobaltK** · 09/09/2016, 20h23

Merci beaucoup !
Je vais regarder tout cela de très près.

**KobaltK** · 10/09/2016, 08h34

Comment supprimer les lignes inutiles ?

Nom : Capture.PNG
Affichages : 598
Taille : 5,3 Ko

**François DORIN** · 10/09/2016, 09h45

En modifiant la requête ainsi

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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
WITH REQUETE AS (
SELECT DISTINCT	E.Champ4 AS CE, 
		S.Libelle, 
		ISNULL(S.GERANT, S.LIBELLE) AS [RANG 1], -- PERMUTE ENTREPRISE ET ST POUR RANG 1
		CASE
			WHEN (S.GERANT IS NULL)
				THEN ''
			WHEN (S.GERANT IS NOT NULL)
				THEN S.LIBELLE
		END AS [RANG 2], -- PERMUTE ENTREPRISE ET ST POUR RANG 2
		E.Champ2 AS Fonction,
		E.IdUtilisateur
	FROM Evenements AS E
	INNER JOIN Societes AS S ON E.IdSociete = S.IdSociete),
DATA AS (
SELECT	R.CE, 
		R.[RANG 1],
		R.[RANG 2],
		SUM(CASE WHEN R.Fonction = 'Encadrant' THEN 1 ELSE 0 END) AS Encadrant, 
		SUM(CASE WHEN R.Fonction = 'Compagnon' THEN 1 ELSE 0 END) AS Compagnon
	FROM REQUETE AS R
	GROUP BY R.CE, R.[RANG 1], R.[RANG 2] WITH ROLLUP),
STAT AS (	
	SELECT D.CE,
	D.[RANG 1],
	D.[RANG 2],
	D.Encadrant,
	D.Compagnon,
	(SELECT COUNT(*) FROM DATA AS E WHERE E.[RANG 1] IS NOT NULL AND E.[RANG 2] IS NOT NULL AND										  
										  E.CE = D.CE AND E.[RANG 1] = D.[RANG 1]) AS 'nb_lignes'
	FROM DATA AS D
)
SELECT S.CE,S.[RANG 1], S.[RANG 2], S.Encadrant, S.Compagnon FROM STAT AS S WHERE S.[RANG 2] IS NOT NULL OR (S.[RANG 2] IS NULL AND S.nb_lignes > 1)

En gros, pour chaque ligne de sous-total (RANG2 est nul), on détermine le nombre de lignes y participant. S'il n'y en a qu'une, le sous-total est filtré.

Maintenant, est-il judicieux de mettre cela au niveau de la requête elle-même, je n'en suis pas sûr. Ce serait plus le rôle d'un post traitement.

**Bernardos** · 10/09/2016, 11h07

Bonjour,
EDIT : j'avais mis une requête mais après analyse, elle est foireuse
Par contre, j'ai voulu ajouter une clé étrangère sur ton idsociete de la table evenement.
mais impossible car dans ton set de données il y a 26 enregistrements pour lesquels le idsociete est à 0 et il n'existe pas de idsociete à 0 dans ta table societe.
Donc pour moi dans ton cas, 2 solutions:

si c'est temporaire (c'est à dire que tu n'as pas encore l'info mais que l'info tu la connaitras un jour) tu peux laisser ce champ à null dans la table evenement et le remplir plus tard.
Sinon tu crées une société "inconnue" avec l'idsociete 0 dans ta table société (note que ca pourrait être n'importe quel id). si tu as plusieurs besoin, tu peux aussi créer plusieurs société genre "inconnue", "en attente" ou "non applicable"

en tout cas, ce qui est sur, pour des raisons de performances et de cohérence des données, il te faut mettre les contraintes sur les clés étrangères!

Cordialement,

2 Boucles WHILE imbriquées pour obtenir des lignes de sous-totaux sur 2 colonnes

MS SQL Server

Discussions similaires

Partager

Partager