Problème somme de valeurs max

**Kyllak** · 20/05/2011, 10h57

Bonjour,

Quasi débutant en SQL, je suis actuellement en stage ou m'a été confié la tâche de suivre les consommations d'eau de l'usine.

Pour ce faire, un capteur analogique me remonte l'information de la consommation tous les x temps en remplissant un tableau de le base de donnée et ce remet à 0 à chaque fin de semaine.

Exemple du tableau:
http://imageshack.us/photo/my-images/228/exempleot.jpg/

Pour avoir ma valeur en fin de semaine il me suffit donc d'afficher la valeur max (qui sera forcément la dernière valeur prise avant la remise à 0)

J'arrive à afficher cette valeur semaine par semaine grâce au code suivant:

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
DECLARE @DEB_AN nvarchar(11)
	SELECT @DEB_AN = 'Jan 01 ' + CAST((DATEPART(year,CURRENT_TIMESTAMP)) AS nvarchar(4))
 
	SELECT 
	DATEPART(year,DateTime) AS ANNEE
	,DATEPART(week,DateTime) AS SEM
	,VALEUR =   CASE
		         WHEN max(value) IS NULL THEN 0
		         ELSE max(value)
		         END	
 
	FROM Runtime.dbo.AnalogHistory 
 
	WHERE
	TagName = 'S13__13PT1_CONSO_ETF'
	AND  DateTime >= @DEB_AN
 
	GROUP BY DATEPART(year,DateTime),DATEPART(week,DateTime)
	ORDER BY DATEPART(year,DateTime),DATEPART(week,DateTime)

Jusque la pas de problème
Maintenant je voudrais afficher cette valeur par mois

Littéralement, il me faut donc "la somme des valeurs max des semaines par mois"

Mais voila si j'affiche simplement la somme par le code suivant:

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
	DECLARE @DEB_AN nvarchar(11)
	SELECT @DEB_AN = 'Jan 01 ' + CAST((DATEPART(year,CURRENT_TIMESTAMP)) AS nvarchar(4))
 
	SELECT 
	DATEPART(year,DateTime) AS ANNEE
	,DATEPART(month,DateTime) AS MOIS
	,VALEUR =	CASE
				WHEN max(value) IS NULL THEN 0
				ELSE sum(value)
				END	
 
	FROM Runtime.dbo.AnalogHistory 
 
	WHERE
	TagName = 'S13__13PT1_CONSO_ETF'
	AND  DateTime >= @DEB_AN
 
	GROUP BY DATEPART(year,DateTime),DATEPART(month,DateTime)
	ORDER BY DATEPART(year,DateTime),DATEPART(month,DateTime)

La valeur que je trouve n'est pas la bonne car il me fait la somme de toutes les valeurs du tableau

Autrement dit si on reprend l'exemple affiché au début il m'indiquerait donc 240 au lieu de 120

Et je ne sais pas comment faire pour contourner ce problème

Merci d'avance pour toutes aides que vous pourrez me fournir

Cordialement

**aieeeuuuuu** · 20/05/2011, 11h58

Bonjour,

Pouvez vous poster la structure de votre table. Quel est le type de la colonne DateTime (est-ce son vrai nom, auquel cas c'est une mauvaise idée : DATETIME est un mot réservé car c'est une type de données)

pouvez vous également préciser votre version de Sql Server

si votre colonne datetime est de type DATETIME, pourquoi déclarer @DEB_AN comme VARCHAR ?

Vous n'avez de toute façon pas besoin d'utiliser une variable, si vous ne voulez les données que depuis le début de l'année en cours, votre filtre peut s’écrire ainsi :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
 
WHERE [datetime] > DATEADD(
    YEAR, 
    DATEDIFF(
        YEAR, 
        0, 
        GETDATE()
    )
    , 0
)

**elsuket** · 20/05/2011, 14h14

Bonjour,

Effectivement vous pouvez simplifier l'écriture de votre requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT	DATEPART(year, DateTime) AS ANNEE
	, DATEPART(week ,DateTime) AS SEM
	, COALESCE(MAX(value), 0) AS CONSO_MAX	
FROM	Runtime.dbo.AnalogHistory 
WHERE	TagName = 'S13__13PT1_CONSO_ETF'
AND	DateTime >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
GROUP	BY DATEPART(year, DateTime), DATEPART(week ,DateTime)
ORDER	BY DATEPART(year, DateTime), DATEPART(week ,DateTime)

Pour une explication sur l'utilisation du zéro avec les fonctions de date, c'est par ici

Mais un problème demeure : une semaine peut être à cheval sur deux mois, et aussi deux années.
Dans ce cas, si nous écrivons :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT	DATEPART(year, DateTime) AS ANNEE
	, DATEPART(month, DateTime) AS MOIS
	, DATEPART(week ,DateTime) AS SEM
	, COALESCE(MAX(value), 0) AS CONSO_MAX	
FROM	Runtime.dbo.AnalogHistory 
WHERE	TagName = 'S13__13PT1_CONSO_ETF'
AND	DateTime >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
GROUP	BY DATEPART(year, DateTime), DATEPART(month, DateTime), DATEPART(week ,DateTime)
ORDER	BY DATEPART(year, DateTime), DATEPART(month, DateTime), DATEPART(week ,DateTime)

Le groupement se faisant sur le mois et la semaine, pour certaines semaines comme les semaines 6, 10 et 14 de cette année (je ne parle que de celles qui sont passées), vous aurez deux lignes.

Dès lors la requête suivante :

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
SELECT	ANNEE
	, MON
	, SUM(CONSO_MAX)
FROM	(
		SELECT	DATEPART(year, DateTime) AS ANNEE
			, DATEPART(month, DateTime) AS MON
			, DATEPART(week ,DateTime) AS SEM
			, COALESCE(MAX(value), 0) AS CONSO_MAX	
		FROM	Runtime.dbo.AnalogHistory 
		WHERE	TagName = 'S13__13PT1_CONSO_ETF'
		AND	DateTime >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
		GROUP	BY DATEPART(year, DateTime), DATEPART(month, DateTime)DATEPART(week ,DateTime)
		ORDER	BY DATEPART(year, DateTime), DATEPART(week ,DateTime)
	) AS S
GROUP	BY ANNEE, MON
ORDER	BY ANNEE, MON

Donne un résultat faux pour les mois dont au moins une semaines est à cheval sur deux mois.

@+

**Kyllak** · 23/05/2011, 09h06

Tout d'abord merci à vous deux vos réponses m'on été très utile

Pour répondre à aieeeuuuuu il ne m'est pas possible de vous expliquer clairement la structure de la table tout simplement parce que je n'y ai pas accès en intégralité, la base de données existant déjà à mon arrivé je manque de visibilité sur ce point
Je ne peux pas non plus modifier cette table

Ce que je sais en revanche c'est que la colonne DateTime porte effectivement ce nom

La version de SQL utilisé est SQL Server Management Studio 2005

Après modification de la requête les valeurs on maintenant l'air de correspondre, en revanche j'ai un petit soucis avec le 1er ORDER BY:

Code :

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

1
2
3
4
5
6
7
GROUP BY DATEPART(year,DateTime),DATEPART(month,DateTime),DATEPART(week,DateTime)
ORDER BY DATEPART(year,DateTime),DATEPART(week,DateTime)
 
		) AS S
 
GROUP BY ANNEE,MON
ORDER BY ANNEE,MON

Lorsque j'exécute la requête j'ai le message d'erreur suivant:
Msg 1033, Level 15, State 1, Line 20
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Lorsque je passe le ORDER BY en commentaire plus de problème

**aieeeuuuuu** · 23/05/2011, 11h24

Bonjour

postez la requête complète.

Visiblement vous faites un "ORDER BY" dans une pseudo table.

postez la requête complète afin que l'on puisse vous aider !

**Kyllak** · 23/05/2011, 11h37

Voici l'intégralité de la requête:

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
USE [WIKINDI]
GO
/****** Object:  StoredProcedure [dbo].[VM_ETF_Pasto13A_YTD]    Script Date: 05/23/2011 11:34:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<FHE>
-- Create date: <23/05>
-- Description:	Consommation mensuel d'ETF 
-- Pasto13A
-- Historique annuel 
-- =============================================
ALTER PROCEDURE [dbo].[VM_ETF_Pasto13A_YTD]
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
SELECT	ANNEE
		,MON
		,SUM(CONSO_MAX)
 
FROM	(
		SELECT	DATEPART(year, DateTime) AS ANNEE
				,DATEPART(month, DateTime) AS MON
				,DATEPART(week ,DateTime) AS SEM
				,COALESCE(MAX(value),0) AS CONSO_MAX	
 
		FROM	Runtime.dbo.AnalogHistory 
 
		WHERE	TagName = 'S13__13PT1_CONSO_ETF'
		AND	DateTime >= DATEADD(year,DATEDIFF(year,0,GETDATE()),0)
 
		GROUP BY DATEPART(year,DateTime),DATEPART(month,DateTime),DATEPART(week,DateTime)
		ORDER BY DATEPART(year,DateTime),DATEPART(week,DateTime)
	) AS S
 
GROUP BY ANNEE, MON
ORDER BY ANNEE, MON	
 
FOR XML AUTO, ELEMENTS, ROOT ('DASH_ETF_13PT1_YTD')
END

Encore merci d'avance

**aieeeuuuuu** · 23/05/2011, 11h55

ok, je n'avais pas vu que c'était issu de la requête d'Elsuket, qui a du travailler sans filet sur ce coup

La clause ~~GROUP BY~~ ORDER BY est inutile dans une sous requete, car les resultats sont destinés a être retraités par la suite. Si le moteur estime qu'il est judicieux de les trier afin d'optimiser le temps de traitement par la suite, il le fera (sans vous demander votre avis

)

enlevez donc le ~~GROUP BY~~ ORDER BY...

Mais le problème soulevé par ElSuket (semaine à cheval sur deux mois) reste entier !

**elsuket** · 23/05/2011, 13h01

ok, je n'avais pas vu que c'était issu de la requête d'Elsuket, qui a du travailler sans filet sur ce coup

C'est effectivement le cas

La clause GROUP BY est inutile dans une sous requete, car les resultats sont destinés a être retraités par la suite

Ce n'est pas tout le temps vrai, et ce n'est pas le cas ici

enlevez donc le GROUP BY...

Non, c'est l'ORDER BY de la sous-requête qu'il faut enlever.
Effectivement celui-ci est inutile.

Mais le problème soulevé par ElSuket (semaine à cheval sur deux mois) reste entier !

Effectivement. Qu'en dites vous Kyllak ?

@++

**aieeeuuuuu** · 23/05/2011, 13h47

arfff

En effet, moi aussi je suis sans filet

Je voulais bien parler de la clause ORDER BY...

J’édite de ce pas

**Kyllak** · 23/05/2011, 14h17

Oui en effet c'est un problème auquel je n'avais pas du tout pensé

A ce moment là j'ais pour idée de dire qu'1 mois = 4 semaines et que tous les mois multiples de 3 = 5 semaines (Janvier=4, Février=4, Mars=5 etc...)
C'est ce qui respecterais le mieux l'objectif du projet, sachant de plus que l'entreprise est organisée en grande partie comme sa.

Je vais d'abords essayer de trouver par moi même sans regarder vos réponses (le but reste quand même de chercher

)

**elsuket** · 23/05/2011, 15h46

A ce moment-là je crois que vous feriez bien d'implémenter un modèle à l'image de celui que donne SQLPro ici, sinon vous allez vous retrouver avec des requêtes un peu immondes ... et pas performantes

@++

**Kyllak** · 25/05/2011, 09h16

Merci beaucoup!

Je mettrais tout sa en place dès que j'en aurais l'occasion

Je marque le sujet comme résolu

Problème somme de valeurs max

Développement SQL Server

Discussions similaires

Partager

Partager