Forcer une colonne à 0 dans une requête FOR XML AUTO [Résolu]

[niko0083]

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 :

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]

Code :

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]

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 :

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

Que nous peuplons avec :

Code :

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

Si nous exécutons :

Code :

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 :

TRUNCATE TABLE dbo.Test

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

Code :

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 :

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]

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]

Envoyé par iberserk

Code :

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]

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 :

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]

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 :

Que nous peuplons avec :

Code :

Si nous exécutons :

Code :

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

Maintenant, purgeons la table avec :

Code :

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 :

Je vous laisse écrire la bonne requête

@++

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

Nicolas.

[aieeeuuuuu]

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 :

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 :

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 :

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 :

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 :

1
2
 
SET DATEFIRST 1

[niko0083]

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

[elsuket]

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 :

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]

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]

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