Appareillage de tables et comparaison

**cobaye13** · 25/05/2015, 20h35

j'ai trois tables
tbl_ReferenceCapacity
RCCycleName
RCBD
RCStep
RCSite
RCAsset

RCQuantity

La clé unique est composé des 5 premiers champs

tbl_SommeCapacityIncrements

CICycleName
CIBD
CIStep
CISite
CIAsset
CIYear
CIQuantity
La clé unique est composée des 6premiers champs (l'année en plus)

tbl_StandStills
STCycleName
STBD
STStep
STSite
STAsset
STQuantity
La clé unique est composée des 6premiers champs (l'année en plus)

les deux tables qui comprennent l'année peuvent toutes deux avoir la même année ou bien l'une peut avoir une année et pas l'autre et vice versa

le résultat attendu est d'avoir une table (ou un formulaire) avec tous les enregistrement de tbl_ReferenceCapacity et le resultat du calcul
RCQuantity = RCQuantity + Quantity SommeCapacityIncrements d'une année - Quantity SommeCapacityStandstills
Si pas d'enregistrement année ni pour une table ni pour l'autre donc pas de changement et année en "blanc" sinon résultat du calcul pour l'année considérée.

Comment pourrait on faire cela ? j'ai bien pensé aux requêtes union mais je séche, une petite piste svp

**bstevy** · 25/05/2015, 22h05

Hmm, a mon avis, le faire avec des UNION est bien la seule méthode, mais vue le nombre de combinatoire, c'est pas forcément évident...

Petite question pour bien être sur de ce qu'il faut faire :

J'ai bien compris que je pouvais avoir une ligne pour une année dans tbl_StandStills et pas dans tbl_SommeCapacityIncrements et inversement.
Par contre, est ce que je peux avoir dans tbl_SommeCapacityIncrements une ligne pour une année, et aussi une ligne sans année ?
J'entends par là : est ce que votre année peut etre null ?

(la question sousjacente est : quel est votre SGBD?, car certain SGBD acceptent une clef composée de valeure nulle, et d'autre non.)

**cobaye13** · 26/05/2015, 07h57

la table standstills et la table capacityincrements peuvent avoir la même année
la table standstills peut avoir des années qui ne sont pas dans la table capacityincrements
la table capacityincrements peut avoir des années qui ne sont pas dans la table standstills
Les trois cas peuvent donc exister.
J'utilise Access
je continue à chercher de mon côté, tuto et essais, mais toute aide est la bienvenue.
Merci

**StringBuilder** · 26/05/2015, 15h06

Euh...

Pour moi, il suffit de faire deux bêtes jointures ouvertes. Avec quelques COALESCE, et le tour est joué !

Code sql :

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

1
2
3
4
5
6
 
select rc.RCCycleName, rc.RCBD, rc.RCStep, rc.RCSite, rc.RCAsset, coalesce(ci.CIYear, st.STYear) Year, rc.RCQuantity, rc.RCQuantity + coalesce(ci.CIQuantity, 0) - coalesce(st.STQuantity, 0) qty
from  tbl_ReferenceCapacity rc
left outer join tbl_SommeCapacityIncrements ci on ci.CICycleName = rc.RCCycleName and ci.CIBD = rc.RCBD and ci.CIStep = rc.RCStep and ci.CISite = rc.RCSite and ci.CIAsset = rc.RCAsset
left outer join tbl_StandStills st on st.STCycleName = rc.RCCycleName and st.STBD = rc.RCBD and st.STStep = rc.RCStep and st.STSite = rc.RCSite and st.STAsset = rc.RCAsset
where coalesce(st.STYear, ci.CIYear) = coalesce(ci.CIYear, st.STYear) or (st.STYear is null and ci.CIYear is null)

J'ai un léger doute sur le WHERE ceci dit...

**StringBuilder** · 26/05/2015, 15h07

COALESCE() sous Access, ça s'appelle peut-être ISNULL()

**bstevy** · 27/05/2015, 02h34

Envoyé par StringBuilder

Pour moi, il suffit de faire deux bêtes jointures ouvertes. Avec quelques COALESCE, et le tour est joué !

Ca ne fonctionnera pas je pense...

si vous avez 2014 d'un coté et 2013 de l'autre, ca va tester l'égalité car aucun n'est null et donc ca ne fera aucune des jointures.

Voici une facon de le faire avec des unions :

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
select	rc.RCCycleName, 
		rc.RCBD, 
		rc.RCStep, 
		rc.RCSite, 
		rc.RCAsset, 
		ci.CIYear	AS	Year, 
		rc.RCQuantity, 
		rc.RCQuantity + ci.CIQuantity	AS	qty
from  tbl_ReferenceCapacity rc
inner join tbl_SommeCapacityIncrements ci	on ci.CICycleName = rc.RCCycleName 
											and ci.CIBD = rc.RCBD 
											and ci.CIStep = rc.RCStep 
											and ci.CISite = rc.RCSite 
											and ci.CIAsset = rc.RCAsset
AND NOT EXISTS	(	SELECT 1
					FROM	tbl_StandStills st
					WHERE	st.STCycleName = rc.RCCycleName 
					and st.STBD = rc.RCBD 
					and st.STStep = rc.RCStep 
					and st.STSite = rc.RCSite 
					and st.STAsset = rc.RCAsset
					AND ci.CIYear = st.STYear
				)
 
UNION ALL
 
select	rc.RCCycleName, 
		rc.RCBD, 
		rc.RCStep, 
		rc.RCSite, 
		rc.RCAsset, 
		st.STYear	AS	Year, 
		rc.RCQuantity, 
		rc.RCQuantity - st.STQuantity	AS	qty
from  tbl_ReferenceCapacity rc
inner join tbl_StandStills st	on st.STCycleName = rc.RCCycleName 
								and st.STBD = rc.RCBD 
								and st.STStep = rc.RCStep 
								and st.STSite = rc.RCSite 
								and st.STAsset = rc.RCAsset
AND NOT EXISTS	(	SELECT 1
					FROM	tbl_SommeCapacityIncrements ci
					WHERE	ci.CICycleName = rc.RCCycleName 
					and ci.CIBD = rc.RCBD 
					and ci.CIStep = rc.RCStep 
					and ci.CISite = rc.RCSite 
					and ci.CIAsset = rc.RCAsset
					AND ci.CIYear = st.STYear
				)
 
UNION ALL
 
select	rc.RCCycleName, 
		rc.RCBD, 
		rc.RCStep, 
		rc.RCSite, 
		rc.RCAsset, 
		st.STYear	AS	Year, 
		rc.RCQuantity, 
		rc.RCQuantity + ci.CIQuantity - st.STQuantity	AS	qty
from  tbl_ReferenceCapacity rc
inner join tbl_StandStills st	on st.STCycleName = rc.RCCycleName 
								and st.STBD = rc.RCBD 
								and st.STStep = rc.RCStep 
								and st.STSite = rc.RCSite 
								and st.STAsset = rc.RCAsset
inner join tbl_SommeCapacityIncrements ci	on ci.CICycleName = rc.RCCycleName 
											and ci.CIBD = rc.RCBD 
											and ci.CIStep = rc.RCStep 
											and ci.CISite = rc.RCSite 
											and ci.CIAsset = rc.RCAsset
WHERE ci.CIYear = st.STYear
 
UNION ALL
 
select	rc.RCCycleName, 
		rc.RCBD, 
		rc.RCStep, 
		rc.RCSite, 
		rc.RCAsset, 
		null	AS	Year, 
		rc.RCQuantity, 
		rc.RCQuantity 	AS	qty
from  tbl_ReferenceCapacity rc
WHERE NOT EXISTS	(	SELECT 1
						FROM	tbl_StandStills st
						WHERE	st.STCycleName = rc.RCCycleName 
						and st.STBD = rc.RCBD 
						and st.STStep = rc.RCStep 
						and st.STSite = rc.RCSite 
						and st.STAsset = rc.RCAsset
					)
AND NOT EXISTS	(	SELECT 1
					FROM	tbl_SommeCapacityIncrements ci
					WHERE	ci.CICycleName = rc.RCCycleName 
					and ci.CIBD = rc.RCBD 
					and ci.CIStep = rc.RCStep 
					and ci.CISite = rc.RCSite 
					and ci.CIAsset = rc.RCAsset
				)
;

Et une autre facon de le faire, plus astucieuse je trouve ^^ mais pas forcément ce qui est attendu car ca affiche toute les anées :

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
select  RCCycleName,
       RCBD, 
       RCStep, 
       RCSite, 
       RCAsset, 
       RCYear,
       sum(QTY)
FROM (
select	rc.RCCycleName, 
		rc.RCBD, 
		rc.RCStep, 
		rc.RCSite, 
		rc.RCAsset, 
		RCYear, 
		rc.RCQuantity as QTY
from  tbl_ReferenceCapacity rc
cross join	(	select ci.CIYear as RCYear from tbl_SommeCapacityIncrements ci
				UNION
				SELECT st.STYear as RCYear from tbl_StandStills st
			)
UNION ALL 
 
SELECT	st.STCycleName 	AS RCCycleName, 
		st.STBD       	AS RCBD, 
		st.STStep     	AS RCStep, 
		st.STSite     	AS RCSite, 
		st.STAsset    	AS RCAsset, 
		st.STYear		AS RCYear,
		-1 * st.STQuantity as QTY
from tbl_StandStills st
 
UNION ALL
 
SELECT	ci.CICycleName AS RCCycleName,
		ci.CIBD       AS RCBD, 
		ci.CIStep     AS RCStep, 
		ci.CISite     AS RCSite, 
		ci.CIAsset    AS RCAsset, 
		ci.CIYear     AS RCYear,
		ci.CIQuantity as QTY
FROM	tbl_SommeCapacityIncrements ci
 
) 
group by RCCycleName,
       RCBD, 
       RCStep, 
       RCSite, 
       RCAsset, 
       RCYear
;

Et le même que précédement mais avec des jointures plutot qu'un group by

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
select	rc.RCCycleName, 
		rc.RCBD, 
		rc.RCStep, 
		rc.RCSite, 
		rc.RCAsset, 
		RCYear, 
		rc.RCQuantity + ci.CIQuantity - st.STQuantity as QTY
from  tbl_ReferenceCapacity rc
cross join	(	select ci.CIYear as RCYear from tbl_SommeCapacityIncrements ci
				UNION
				SELECT st.STYear as RCYear from tbl_StandStills st
			)
left outer join tbl_StandStills st on st.STCycleName = rc.RCCycleName 
								and st.STBD = rc.RCBD 
								and st.STStep = rc.RCStep 
								and st.STSite = rc.RCSite 
								and st.STAsset = rc.RCAsset
								AND RCYear = st.STYear
left outer join tbl_SommeCapacityIncrements ci on ci.CICycleName = rc.RCCycleName 
											and ci.CIBD = rc.RCBD 
											and ci.CIStep = rc.RCStep 
											and ci.CISite = rc.RCSite 
											and ci.CIAsset = rc.RCAsset
											AND RCYear = ci.CIYear

**cobaye13** · 27/05/2015, 08h12

Bonjour et merci à tous ceux qui m'ont répondu.
Tout d'abord je me suis débrouillé seul en faisiant une bête requête union et ensuite une requête regroupement avec une somme (j'ai préalablement multiplié par moins 1 la valeur à soustraire) et puis une jointure avec ReferenceCapacity.
Ok c'est moins beau mais j'étais pressé.
Vous m'avez appris beaucoup.
je ne connaissais pas coalesce je vais tester cela dès que j'ai un moment mais en effet je ne pense pas que ça marche dans mon cas.
Je vais regarder ce qu'a écrit bstevy cela a l'air plus joli que ce que j'ai fait.

**bstevy** · 27/05/2015, 08h43

Envoyé par cobaye13

une bête requête union et ensuite une requête regroupement avec une somme (j'ai préalablement multiplié par moins 1 la valeur à soustraire) et puis une jointure avec ReferenceCapacity.

Votre solution à l'air très bien.

A mon avis, vous n'avez pas besoin de vous plonger dans mes requetes.
Il y a plein de facon de traiter un cas en général, j'en ai cité 3, vous en avez cité une 4 eme, il en existe sans doute d'autre. Et je pense que votre facon de faire est très élégante

Le seul cas où vous auriez à vous penchez sur une de mes requête, c'est si vous avez des problèmes de perf : une autre écriture peut parfois résoudre bien des problèmes.

Appareillage de tables et comparaison

Langage SQL

Discussions similaires

Partager

Partager