Problème différences de valeurs

[Kyllak]

Bonjour,

J'ai actuellement un problème pour faire la différence entre 2 valeurs d'une table
Tout d'abord voici la requête:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT	DATEPART(year, DateTime) AS ANNEE
	,DATEPART(week ,DateTime) AS SEM
	,ETF1 = CASE
		WHEN max(value) IS NULL THEN 0
		ELSE max(value)-min(value)
		END
 
FROM	Runtime.dbo.AnalogHistory 
 
WHERE	TagName = 'RWT_CPT_ETCF'
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)

Et le résultat renvoyé:
http://imageshack.us/photo/my-images/233/tabley.png/

Ici, le problème est que le capteur qui me remonte l'information (données de la consommation d'eau sur une ligne) se remet à 0 tous les 9999m3

Du coup, peut être l'aurais vous déjà remarqué au niveau des résultats renvoyés, chaque fois que la valeur se remet à 0 le résultat de la différence entre la valeur max et min est faussé (pour exemple on peut voir que la valeur 9257 en semaine 3)

Juste pour info voici le résultat renvoyé si je demande uniquement la valeur max:

Code :

VALEUR_MAX =max(value)

Me renvoie le résultat:
http://imageshack.us/photo/my-images/109/table2e.png/

Merci de toute aide que vous pourriez me fournir

[Sergejack]

Envoyé par Kyllak

Ici, le problème est que le capteur qui me remonte l'information (données de la consommation d'eau sur une ligne) se remet à 0 tous les 9999m3

Il vous faut donc avant ce traitement suffisamment corriger la perte de précision de vos données.

Vous utilisez quelle version ?

[Kyllak]

Tout d'abord merci de prêter attention à mon sujet
La version SQL utilisé est la 2005

[Sergejack]

Est-ce que votre objectif est de mesurer la consommation hebdomadaire ?
Auquel cas, il vous faut mesurer non pas la différence entre la plus grande et la plus petite valeur dans la semaine mais la différence entre la dernière valeur de la semaine et celle de la semaine précédente.

[Kyllak]

En effet c'est bien une historisation hebdomadaire qui m'intéresse et c'est dans ce cas où j'ais du mal à formuler ma requête

[7gyY9w1ZY6ySRgPeaefZ]

Avant de chercher compliqué, c'est quoi le type de Value ?

[Sergejack]

Le plus simple (en terme d'écriture et de lecture du query) est sans doute de faire une CTE récursive.

Je pense qu'une table variable correctement utilisée pourrait aussi être utile dans votre cas.

Code :

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
DECLARE @tbl TABLE (
 ROW INT PRIMARY KEY
 , ANNEE INT
 , SEM INT
 , Value DECIMAL(??, ??)

 , UNIQUE (TSEM, ANNEE, SEM, ROW)
 , UNIQUE (TSEM, ROW)
)

INSERT @tbl (
 ROW
 , ANNEE
 , SEM
 , Value
)
 SELECT
 ROW_NUMBER() OVER (ORDER BY DateTime ASC) AS ROW
 , DATEPART(year, DateTime) AS ANNEE
 , DATEPART(week ,DateTime) AS SEM
 , DATEDIFF(week, 0, DateTime) AS TSEM
 , Value
FROM	Runtime.dbo.AnalogHistory 
WHERE	TagName = 'RWT_CPT_ETCF'
AND	DateTime >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)

; WITH CTE AS (
SELECT
T.ANNEE
, T.SEM
, T.TSEM 
, T.ROW
, ISNULL(T2.Value, 0) AS Value
, 0.0 AS Diff
FROM (
SELECT
ANNEE
, SEM
, TSEM 
,  MIN(ROW) AS ROW
FROM @tbl
GROUP BY
ANNEE
, SEM
, TSEM 
) AS T
LEFT JOIN @tbl AS T2 ON (
 T2.ROW = T.ROW - 1
)

UNION ALL

SELECT
T.ANNEE
, T.SEM
, T.TSEM 
, T2.ROW
, V.Value
, T.Diff + ISNULL(V.Value, T.Value) - T.Value
FROM CTE AS T
LEFT JOIN @tbl AS T2 ON (
 T2.ROW = T.ROW + 1 -- Ligne suivante
 AND
 T2.TSEM = T.TSEM -- Même semaine
)
OUTER APPLY (
 SELECT CASE T2.Value < T.Value THEN T2.Value + 1000 ELSE T2.Value END
) V(Value)
WHERE T.Value IS NOT NULL
)

SELECT
ANNEE
, SEM
, Diff
FROM CTE 
WHERE Value IS NULL

[iberserk]

Bonjour,
-1 pour la colonne nommée datetime!

Sinon vous avez un relevé journalier des consommations?
Il est aisé de savoir quand le compteur se remet à 0 (valeur du jour< valeur de la veille... en partant du principe que vous ne pouvez faire plus de 10000 dans la journée)...

A partir de là le reste est trivial...

[Sergejack]

Envoyé par iberserk

A partir de là le reste est trivial...

Ou pas...

[aieeeuuuuu]

Bonjour

Avec une auto jointure, pour vérifier la valeur "précédente" (pas testé):

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
 
;WITH CTE AS (
	SELECT	
		[DateTime] AS DateHeure,
		value,
		ROW_NUMBER() OVER (ORDER BY [DateTime]) AS RN
	FROM	Runtime.dbo.AnalogHistory 
	WHERE	TagName = 'RWT_CPT_ETCF'
)
SELECT 
	DATEPART(year, C1.DateHeure) AS ANNEE,
	DATEPART(week ,C1.DateHEure) AS SEM,
	SUM (
		CASE 
			WHEN C1.value > COALESCE(C2.value, 0) THEN 
				c1.value - c2.value
			ELSE
				c1.value - c2.value + 10000
		END
	) AS Conso 
FROM CTE C1
LEFT OUTER JOIN CTE C2 --ligne "précédente" par rapport à datetime
	ON C1.RN  = C2.RN + 1
WHERE C1.DateHeure >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
GROUP BY DATEPART(year, DateTime), DATEPART(week ,DateTime)
ORDER BY DATEPART(year, DateTime), DATEPART(week ,DateTime)

Le filtre sur la date pourrait être mis dans la CTE, mais il manquerait alors la valeur précédente de la première ligne, ce qui peut être corrigé autrement (par exemple une requête dans le COALESCE...)...