Forcer une colonne à 0 dans une requête FOR XML AUTO

**niko0083** · 28/03/2011, 09h25

Bonjour,

Après avoir recherché et galéré pas mal de temps je reste sans solution à mon problème.

J'exécute sous SQL Serveur des procédures stockées qui me génèrent des résultats dans un fichier XML (via BCP) qui est ensuite utilisé pour faire du repporting.
Ces procédures vont récupérer des informations dans diverses bases de données historisant ce qui se passe la semaine.
Or lorsque mes bases de données sont vides (en début de semaine) mes querry n'affectent aucunes colonnes, le fichier XML généré et vide/corrompu et fait planter ma vue de repporting.

J'ai essayé de faire des SELECT imbriqués, et des CASE pour forcer une colonne à zéro si il n’y a pas de résultat mais cela ne marche pas, connaissez vous une autre solution???

Extrait de la querry :

Code :

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

1
2
3
4
5
6
7
SELECT	date ,SUM(CAST(Valeur AS float)/1000) AS NUM
FROM BASE.dbo.WIK_QteHebdo
WHERE	datepart(year,date) >= @tkan 
	and datepart(month,date) >= @tkmois 
	and datepart(day,date) >= @tkjour	
GROUP BY date 
FOR XML AUTO, ELEMENTS, ROOT ('NUM')

Nicolas.

**iberserk** · 28/03/2011, 17h17

Code :

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

1
2
3
4
5
6
7
SELECT	date ,SUM(CAST(Valeur AS float)/1000) AS NUM
FROM BASE.dbo.WIK_QteHebdo
WHERE	datepart(year,date) >= @tkan 
	AND datepart(month,date) >= @tkmois 
	AND datepart(day,date) >= @tkjour	
GROUP BY date 
FOR XML AUTO, ELEMENTS, ROOT ('NUM')

Dans le cas ou cette requête ne vous renvois rien vous voudriez tout de même avoir une ligne?

**elsuket** · 28/03/2011, 17h34

Bonjour,

J'exécute sous SQL Serveur des procédures stockées qui me génèrent des résultats dans un fichier XML (via BCP) qui est ensuite utilisé pour faire du repporting.

C'est dommage, vous auriez pu utiliser SQL Server Reporting Services qui est livré avec SQL Server dans les éditions Standard et Enterprise ...

J'ai essayé de faire des SELECT imbriqués, et des CASE pour forcer une colonne à zéro si il n’y a pas de résultat mais cela ne marche pas, connaissez vous une autre solution???

C'est normal, puisque votre ensemble est vide

Si on calcule la date du premier jour de la semaine et qu'on met zéro pour la somme, ça vous convient ?

Soit alors la table suivante :

Code :

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

1
2
3
4
5
CREATE TABLE test
(
	une_date datetime
	, une_valeur int
)

Que nous peuplons avec :

Code :

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

1
2
INSERT	dbo.test
SELECT	CAST('20110328' AS datetime), 12

Si nous exécutons :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT	une_date
	, SUM(valeur_totale) AS valeur_totale
FROM	(
			SELECT	une_date
				, SUM(une_valeur) AS valeur_totale
			FROM	dbo.Test
			GROUP	BY une_date
		UNION ALL
			SELECT	DATEADD(week, DATEDIFF(week, CAST(0 AS datetime), GETDATE()), CAST(0 AS datetime))
				, 0
	) AS T
GROUP	BY une_date

Nous obtenons 2011-03-28 00:00:00.000 12

Maintenant, purgeons la table avec :

Code :

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

TRUNCATE TABLE dbo.Test

Et réexécutons la requête : on obtient alors : 2011-03-28 00:00:00.000 0

Code :

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

1
2
3
4
5
6
7
SELECT	date ,SUM(CAST(Valeur AS float)/1000) AS NUM
FROM BASE.dbo.WIK_QteHebdo
WHERE	datepart(year,date) >= @tkan 
	AND datepart(month,date) >= @tkmois 
	AND datepart(day,date) >= @tkjour	
GROUP BY date 
FOR XML AUTO, ELEMENTS, ROOT ('NUM')

Jusqu'au WHERE ça va, mais ensuite c'est une catastrophe, parce que vos filtres ne sont pas cherchables : SQL Server maintient bien des statistiques sur la colonne date (avec un peu de chance vous l'avez probablement indexée), mais pas sur le mois des valeurs de cette colonne, ni sur l'année, ni sur le jour ...

Pour faire ça proprement, vous pouvez donc écrire :

Code :

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

1
2
3
4
5
6
SELECT	date
	, SUM(CAST(Valeur AS float)/1000) AS NUM
FROM	BASE.dbo.WIK_QteHebdo
WHERE	date >  CAST(CAST(@tkan AS varchar(4)) + CAST(@tkmois AS varchar(2)) + CAST(@tkjour AS varchar(2)) AS datetime)
GROUP	BY date 
FOR XML AUTO, ELEMENTS, ROOT ('NUM')

Je vous laisse écrire la bonne requête

@++

**niko0083** · 28/03/2011, 21h21

Merci pour votre réponse rapide, je test tout ça demain matin au boulot, je vous tiens au courant !!!

Au niveau du WHERE j'ai fais de cette façon car le mois de la colonne date est formaté comme suit : "03" alors que le ma variable @TKMOIS renvoie juste "3". Le DATEPART(month,Date) me permet de renvoyer la valeur "3" comme pour la variable @TKMOIS.

Je vous posterais (par curiosité) demain matin l'élaboration de mes variables @TKXX (c'est une usine à gaz mais j'ai pas trouvé plus simple

)

Nicolas.

**niko0083** · 28/03/2011, 21h55

Envoyé par iberserk

Code :

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

1
2
3
4
5
6
7
SELECT	date ,SUM(CAST(Valeur AS float)/1000) AS NUM
FROM BASE.dbo.WIK_QteHebdo
WHERE	datepart(year,date) >= @tkan 
	AND datepart(month,date) >= @tkmois 
	AND datepart(day,date) >= @tkjour	
GROUP BY date 
FOR XML AUTO, ELEMENTS, ROOT ('NUM')

Dans le cas ou cette requête ne vous renvois rien vous voudriez tout de même avoir une ligne?

Oui car j'utilise ce fichier XML dans une vue crée dans Xcelsius, et si cette vue est liée à un XML vide elle renvoie un message d'erreur.
A part ce petit soucis, Xcelsius reste un outils vraiment pratique et ergonomique, je le conseil aux personnes créant des vues de repporting.

**niko0083** · 29/03/2011, 08h52

Voici l'élaboration de mes variable; le but étant de sélectionner les dates de la semaine chaque semaine, le correctif servant a afficher les dates pour les semaines à cheval sur 2 mois.

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
set datefirst 1
DECLARE @JOUR int
DECLARE @REF int
DECLARE @TKAN nchar(4)
DECLARE @TKMOIS nchar(2)
DECLARE @TKJOUR nchar(2)
DECLARE @TKO nchar(20)
SELECT @JOUR = (DATEPART(weekday,getdate()))
SELECT @TKAN = (DATEPART(year, getdate()))
 
------------------------ correctif passage de mois---------------------------------------
SELECT @REF =(CASE (DATEPART(month, getdate())) WHEN 2 THEN (31)WHEN 3 THEN (28)WHEN 4 THEN (31)WHEN 5 THEN (30)WHEN 6 THEN (31)WHEN 7 THEN (30)WHEN 8 THEN (31)WHEN 9 THEN (31)WHEN 10 THEN (30)WHEN 11 THEN (31)WHEN 12 THEN (30)WHEN 1 THEN (31)end)									
SELECT @TKMOIS=(CASE when ((cast(datename(week,getdate()) as int)- cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1)=1 ) THEN  (DATEPART(month, getdate())-1) else (DATEPART(month, getdate())) end)
SELECT @TKJOUR=(CASE  when ((cast(datename(week,getdate()) as int)- cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1)=1 )THEN   (@ref -@jour+(DATEPART(day, getdate()))+1) else (DATEPART(day, getdate())-@jour+1) end)
SELECT @TKO = @TKAN+'.'+@TKMOIS+'.'+@TKJOUR
------------------------ correctif passage de mois---------------------------------------

**niko0083** · 29/03/2011, 09h18

Envoyé par elsuket

Bonjour,

C'est dommage, vous auriez pu utiliser SQL Server Reporting Services qui est livré avec SQL Server dans les éditions Standard et Enterprise ...

C'est normal, puisque votre ensemble est vide

Si on calcule la date du premier jour de la semaine et qu'on met zéro pour la somme, ça vous convient ?

Soit alors la table suivante :

Code :

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

1
2
3
4
5
CREATE TABLE test
(
	une_date datetime
	, une_valeur int
)

Que nous peuplons avec :

Code :

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

1
2
INSERT	dbo.test
SELECT	CAST('20110328' AS datetime), 12

Si nous exécutons :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT	une_date
	, SUM(valeur_totale) AS valeur_totale
FROM	(
			SELECT	une_date
				, SUM(une_valeur) AS valeur_totale
			FROM	dbo.Test
			GROUP	BY une_date
		UNION ALL
			SELECT	DATEADD(week, DATEDIFF(week, CAST(0 AS datetime), GETDATE()), CAST(0 AS datetime))
				, 0
	) AS T
GROUP	BY une_date

Nous obtenons 2011-03-28 00:00:00.000 12

Maintenant, purgeons la table avec :

Code :

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

TRUNCATE TABLE dbo.Test

Et réexécutons la requête : on obtient alors : 2011-03-28 00:00:00.000 0

Jusqu'au WHERE ça va, mais ensuite c'est une catastrophe, parce que vos filtres ne sont pas cherchables : SQL Server maintient bien des statistiques sur la colonne date (avec un peu de chance vous l'avez probablement indexée), mais pas sur le mois des valeurs de cette colonne, ni sur l'année, ni sur le jour ...

Pour faire ça proprement, vous pouvez donc écrire :

Code :

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

1
2
3
4
5
6
SELECT	date
	, SUM(CAST(Valeur AS float)/1000) AS NUM
FROM	BASE.dbo.WIK_QteHebdo
WHERE	date >  CAST(CAST(@tkan AS varchar(4)) + CAST(@tkmois AS varchar(2)) + CAST(@tkjour AS varchar(2)) AS datetime)
GROUP	BY date 
FOR XML AUTO, ELEMENTS, ROOT ('NUM')

Je vous laisse écrire la bonne requête

@++

Merci bien, apparemment cela fonctionne j'attends la semaine prochaine pour confirmer

Nicolas.

**aieeeuuuuu** · 29/03/2011, 12h25

Envoyé par niko0083

Voici l'élaboration de mes variable; le but étant de selectionner les dates de la semaine chaque semaine, le correctif servant a afficher les dates pour les semaines à cheval sur 2 mois.

Si j'ai bien compris votre script, il s'agit de retrouver la date du début de la semaine en cours (le dernier lundi en fait) et de le formater (AAAA.M.J) ?

vous semblez gérer les semaines à cheval sur deux mois, mais celles à cheval sur deux années ?

Et les années bisextiles (WHEN 3 THEN (28)... ) ?

De plus, pour votre formatage, il semble y avoir une espace entre le mois et le point pour les mois sur un chiffre. Est-ce normal ?

Pour obtenir le lundi de la semaine en cours, vous pouvez faire comme ceci :

Code SQL :

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

1
2
3
4
5
6
 
SELECT DATEADD(
	DAY,
	-DATEPART(WEEKDAY,GETDATE()) + 1,
	GETDATE()
)

et donc, je pense que vous pouvez calculer votre @TKO simplement comme ça :

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
 
SET @TKO =
	REPLACE(
		CONVERT(
			NCHAR(20),
			DATEADD(
				DAY,
				-DATEPART(WEEKDAY,GETDATE()) + 1,
				GETDATE()
			),
			102
		),
		'.0',
		'.'
	)

ou bien comme ceci, dédicacée à ElSuket pour la manipulation des dates avec des fonctions date exclusivement

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
34
 
SELECT @TKO =
CAST(
	DATEPART(YEAR,
		DATEADD(
			DAY,
			-DATEPART(WEEKDAY,GETDATE()) + 1,
			GETDATE()
			)
		)
	AS VARCHAR(20)
)
+ '.' +
CAST(
	DATEPART(MONTH,
		DATEADD(
			DAY,
			-DATEPART(WEEKDAY,GETDATE()) + 1,
			GETDATE()
			)
		)
	AS VARCHAR(20)
)
+ '.' +
CAST(
	DATEPART(DAY,
		DATEADD(
			DAY,
			-DATEPART(WEEKDAY,GETDATE()) + 1,
			GETDATE()
			)
		)
	AS VARCHAR(20)
)

Qui est peut-être plus lisible 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
 
DECLARE @TKO  NCHAR(20)
DECLARE @DATEREF DATETIME --dernier lundi en date
 
SET @DATEREF = DATEADD(
		DAY,
		-DATEPART(WEEKDAY,GETDATE()) + 1,
		GETDATE()
		)
 
 
SET @TKO =
	CAST(DATEPART(YEAR,@DATEREF) AS VARCHAR(20))
	+ '.' +
	CAST(DATEPART(MONTH,@DATEREF) AS VARCHAR(20))
	+ '.' +
	CAST(DATEPART(DAY,@DATEREF) AS VARCHAR(20))
 
SELECT @TKO

Bien sûr à chaque fois avec un

Code SQL :

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

1
2
 
SET DATEFIRST 1

**niko0083** · 29/03/2011, 13h43

Grand merci a vous, je pense que l'on cloturer sujet, mais il faut que je trouve comment

**elsuket** · 29/03/2011, 14h44

il s'agit de retrouver la date du début de la semaine en cours (le dernier lundi en fait) et de le formater (AAAA.M.J) ?

Pour faire plus court :

Code :

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

SELECT CONVERT(char(10), DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0), 102)

Encore une fois on manipule les dates avec les fonctions de date !

@++

**aieeeuuuuu** · 29/03/2011, 14h51

sauf qu'il ne veut pas les 0 non significatifs en début de mois (et de jour ?) :

Il ne veut pas

2011.03.29

mais

2011.3.29

**niko0083** · 30/03/2011, 12h10

C'est juste, le formatage avec les zérso était une contrainte, merci pour vos solutions.

Forcer une colonne à 0 dans une requête FOR XML AUTO

Développement SQL Server

Discussions similaires

Partager

Partager